qury help - SQL Server Forums

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)

qury help

Author

Topic

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2010-04-26 : 08:49:04

i have data that is returning

5555\555\555\5555.jpg

how can i do a select to just return me the 5555.jpg and not everything before the last slash?

karthik_padbanaban
Constraint Violating Yak Guru

263 Posts

Posted - 2010-04-26 : 08:53:33

may be this can help..

but there should be a better way.

declare @a varchar(50)
set @a = '5555\555\555\5555.jpg'
select reverse(substring(reverse(@a),1,charindex('\',reverse(@a),1)-1))

Karthik
http://karthik4identity.blogspot.com/

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2010-04-26 : 08:53:43

[code]
SELECT REVERSE(SUBSTRING(REVERSE('5555\555\555\5555.jpg'),1,CHARINDEX('\',REVERSE('5555\555\555\5555.jpg'))))
[/code]

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2010-04-26 : 09:01:37

Just 10 sec!

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2010-04-26 : 09:07:38

Also

SELECT RIGHT(value, CHARINDEX('\',REVERSE(value))-1)

esthera
Master Smack Fu Yak Hacker

1410 Posts

Posted - 2010-04-26 : 09:21:52

SELECT REVERSE(SUBSTRING(REVERSE('5555\555\555\5555.jpg'),1,CHARINDEX('\',REVERSE('5555\555\555\5555.jpg'))))

this returns \5555.jpg

how do i return just 5555.jpg

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2010-04-26 : 09:22:58

quote:
Originally posted by esthera

SELECT REVERSE(SUBSTRING(REVERSE('5555\555\555\5555.jpg'),1,CHARINDEX('\',REVERSE('5555\555\555\5555.jpg'))-1))

this returns \5555.jpg

how do i return just 5555.jpg

karthik_padbanaban
Constraint Violating Yak Guru

263 Posts

Posted - 2010-04-26 : 09:25:57

quote:
Originally posted by ms65g

Also
SELECT RIGHT(@a, CHARINDEX('\',REVERSE(@a))-1)

yes, this is what I ment as a better way.

Karthik
http://karthik4identity.blogspot.com/

Sachin.Nand

2937 Posts

Posted - 2010-04-26 : 09:45:43

declare @a varchar(50)
set @a = '5555\555\555\5555.jpg'
select replace(PARSENAME(replace(replace(@a,'.',' '),'\','.'),1),' ','.')

PBUH

karthik_padbanaban
Constraint Violating Yak Guru

263 Posts

Posted - 2010-04-27 : 03:33:28

quote:
Originally posted by Idera

declare @a varchar(50)
set @a = '5555\555\555\5555.jpg'
select replace(PARSENAME(replace(replace(@a,'.',' '),'\','.'),1),' ','.')

PBUH

Hi this will not give the correct result once the number of slash("\") increase beyond 4. parsename only take 4 part name or less than that.

and I do prefer this would provide the correct one always.

SELECT RIGHT(@a, CHARINDEX('\',REVERSE(@a))-1)

Karthik
http://karthik4identity.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources