Help in substring 12_Abc_tas.txt get Abc_tas.tx

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 2000 Forums

Transact-SQL (2000)

Help in substring 12_Abc_tas.txt get Abc_tas.tx

Author

Topic

kamii47
Constraint Violating Yak Guru

353 Posts

Posted - 2007-09-21 : 06:17:25

I have a field in my data base as a file name which save as primarykeyid + '_' + filename
Now from the tsql i want's to just get the name of the fiel how can i get it via substring funtion
Suppose in db it is saved as 12_Test.txt I want's Texst.txt
or 1_Test_asdasdasd_sd.bmp I want's Test_asdasdasd_sd.bmp

How can i do this ?

Kamran Shahid
Software Engineer(MCSD.Net)
www.netprosys.com

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-09-21 : 06:20:56

[code]SELECT value, right(value, LEN(value) - CHARINDEX('_', value))
FROM (
SELECT value = '12_Test.txt' UNION ALL
SELECT value = '1_Test_asdasdasd_sd.bmp'
) t
/*
value
----------------------- -----------------------
12_Test.txt Test.txt
1_Test_asdasdasd_sd.bmp Test_asdasdasd_sd.bmp
*/[/code]

KH
[spoiler]Time is always against us[/spoiler]

kamii47
Constraint Violating Yak Guru

353 Posts

Posted - 2007-09-21 : 07:09:38

Thnaks
I just needed this
right('Kamran_shahid_text.txt', LEN('Kamran_shahid_text.txt') - CHARINDEX('_', 'Kamran_shahid_text.txt')

Kamran Shahid
Software Engineer(MCSD.Net)
www.netprosys.com

Kristen
Test

22859 Posts

Posted - 2007-09-21 : 07:21:57

Note: You should use DATALENGTH() instead of LEN() in case the values have trailing spaces

Kristen

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-09-21 : 07:26:12

The problem happens when you use LEN on CHAR datatype column

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources