delete part of the fields - 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

General SQL Server Forums

New to SQL Server Programming

delete part of the fields

Author

Topic

a.ashabi
Posting Yak Master

117 Posts

Posted - 2009-01-05 : 16:46:00

Hi.I have a table with diffrent values like this:
14280_aljuc_1.jpg
14280_aljuc_3.jpg
14280_LBL.jpg
14280_SUP.jpg
100_Fkidbld_3.jpg
110_Fkidbld_1.jpg
1001_Soothing_1.jpg
10100_Half_1.jpg
...

if there is a middle part I want to delete that.which means I need to have these results:
14280_1.jpg
14280_3.jpg
14280_LBL.jpg
14280_SUP.jpg
100_3.jpg
110_1.jpg
1001_1.jpg
10100_1.jpg

plz help.thanks

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2009-01-05 : 19:08:39

As long as you want everything to the right of the first "_"
declare @var varchar(80)

set @var = '10100_Half_1.jpg'

select right(@var,len(@var)-charindex('_',@var))

Jim

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-01-05 : 21:13:55

Here is my shot:

declare @t table
(pic varchar(100))

insert @t
select '14280_aljuc_1.jpg' union all
select '14280_LBL.jpg' union all
select '14280_LBL.jpg' union all
select '14280_SUP.jpg' union all
select '100_3.jpg' union all
select '110_1.jpg' union all
select '1001_1.jpg' union all
select '10100_1.jpg'

SELECT case when pic like '[0-9]%_[a-z]%_[0-9]%.jpg' then STUFF(pic, charindex('_',pic)+1,
len(pic)- charindex('_',pic), right(pic,5))else pic end
from @t

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources