get extension from a string [RESOLVED] ty

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

get extension from a string [RESOLVED] ty

Author

Topic

Pace
Constraint Violating Yak Guru

264 Posts

Posted - 2009-03-10 : 06:43:36

Hi Experts,

I am trying to allow searching of file extensions.

I am passing in a string. However I am struggling as I cannot be sure if the extension is 3 or 4 characters long.

So say I had;
"this is some test.pdf" I want the last 3. I can do that.

However I may get this situation ;
"this is some test.tiff" here I would currently return "iff"

My question is how do I analyse the initial input string and trim only what i'm interested in. So I could even get something like;

"this is some test.custom" return "custom"

Thanks for your help as always ;)
Pace

"Impossible is Nothing"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-10 : 06:48:28

just use this


SELECT RIGHT(@string,LEN(@String)-CHARINDEX('.',@string))

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-03-10 : 06:49:05

is this u want as required output
select substring('this is some test.custom',charindex('.','this is some test.custom',1)+1,len('this is some test.custom'))

Pace
Constraint Violating Yak Guru

264 Posts

Posted - 2009-03-10 : 06:58:16

Both solutions work fantastically. Thank you both.

Can I be cheeky? Is it possible to extend this so that it only counts from the last "." ?

Is that possible.

In case someone accidentally had;
"somefile.data.pdf"

Still returns ".pdf"

"Impossible is Nothing"

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-03-10 : 07:03:06

try this one
select substring(@string,len(@string)-charindex('.',reverse(@string),1)+2,len(@string))

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-03-10 : 07:05:42

Try this another way

declare @var varchar(32)
set @var ='this is some test.iwant'
select reverse(substring(reverse(@var),1,charindex('.',reverse(@var))-1)) as value

(or)

select parsename(@var,1)

Pace
Constraint Violating Yak Guru

264 Posts

Posted - 2009-03-10 : 07:09:38

Thank you all so much

"Impossible is Nothing"

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-03-10 : 07:10:50

welcome

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-03-10 : 07:13:33

Welcome

darkdusky
Aged Yak Warrior

591 Posts

Posted - 2009-03-10 : 07:21:03

If there is a chance of other full stops in the string - we can add REVERSE to Visakh's select statement, e.g.

declare @String varchar (1000)
set @String = 'a.book1.tiff'
SELECT Right(@String,CHARINDEX('.',REVERSE(@String))-1)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-03-10 : 08:41:11

quote:
Originally posted by darkdusky

If there is a chance of other full stops in the string - we can add REVERSE to Visakh's select statement, e.g.

declare @String varchar (1000)
set @String = 'a.book1.tiff'
SELECT Right(@String,CHARINDEX('.',REVERSE(@String))-1)

This is the approach I usually suggest in this case

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-10 : 13:10:56

quote:
Originally posted by madhivanan

quote:
Originally posted by darkdusky

If there is a chance of other full stops in the string - we can add REVERSE to Visakh's select statement, e.g.

declare @String varchar (1000)
set @String = 'a.book1.tiff'
SELECT Right(@String,CHARINDEX('.',REVERSE(@String))-1)

This is the approach I usually suggest in this case

Madhivanan

Failing to plan is Planning to fail

Yup..i learnt it from you

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources