select only some part of result

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)

select only some part of result

Author

Topic

PatDeV
Posting Yak Master

197 Posts

Posted - 2009-01-25 : 17:10:19

Hi all,

I have data in column like this

c:/testfolder/test/folder/datafile/testresult.xls.

and i only want to select testresult.xls what shoud i use!!

thanks

nr
SQLTeam MVY

12543 Posts

Posted - 2009-01-25 : 19:35:32

seect right(col, charindex('/',reverse(col))-1) from tbl

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

PatDeV
Posting Yak Master

197 Posts

Posted - 2009-01-25 : 19:41:47

it says that Invalid length parameter passed to the RIGHT function.

nr
SQLTeam MVY

12543 Posts

Posted - 2009-01-25 : 20:21:27

then there must be someentries without a '/'
try
select right(col, charindex('/',reverse(col))-1)
from tbl
where col like '%/%'
and right(col,1) <> '/'

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2009-01-25 : 20:25:30

[code]select case when charindex('/',col) > 0
then reverse(left(reverse(col),charindex('/',reverse(col))-1))
else col end[/code]

nr
SQLTeam MVY

12543 Posts

Posted - 2009-01-25 : 20:35:11

if this is important there are lots of options (plus a correction for the above)
Think you should post what you want before everyone jumps in with slight amendments to the initial query.

some possibilities.

select case when charindex('/',col) > 1
then reverse(left(reverse(col),charindex('/',reverse(col))-1))
else col end

select case when charindex('/',col) > 1
then reverse(left(reverse(col),charindex('/',reverse(col))-1))
else 'invalid' end

select reverse(left(reverse(col),charindex('/',reverse(col))-1))
where charindex('/',col) > 1

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-26 : 08:43:09

also

select stuff(col,1,len(col)-charindex('/',reverse(col))+1,'') from table

PatDeV
Posting Yak Master

197 Posts

Posted - 2009-01-26 : 09:20:51

i only get blank value in result

the data is like this c:/temp/folder/datafile/filename.xls and only need to get if filename.xls.

thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-26 : 09:24:18

select stuff(col,1,len(col)-case when charindex('/',reverse(col))>0 then charindex('/',reverse(col))+1 else 0 end,'') from table

PatDeV
Posting Yak Master

197 Posts

Posted - 2009-01-26 : 09:56:50

still getting blank!! Don;t know why!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-27 : 01:04:41

[code]
select case when charindex('/',reverse(col))>0 then stuff(col,1,len(col)-charindex('/',reverse(col))+1,'') else col end
from (select 'c:/testfolder/test/folder/datafile/testresult.xls.' as col
union all select 'f:/test.xls'
union all select 'table1.xls')t

output
------------------------------
testresult.xls.
test.xls
table1.xls[/code]

Rajesh Jonnalagadda
Starting Member

45 Posts

Posted - 2009-01-28 : 08:25:22

Try this,

SELECT REVERSE(SUBSTRING(REVERSE('c:/testfolder/test/folder/datafile/testresult.xls'),1,CHARINDEX('/', REVERSE('c:/testfolder/test/folder/datafile/testresult.xls'))-1))

Rajesh Jonnalagadda
[url="http://www.ggktech.com
"]GGK TECH[/url]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources