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
 replace only some underscores

Author  Topic 

sravan.here
Starting Member

4 Posts

Posted - 2014-02-21 : 15:58:51
I have a column with value

AAA_ZZZZ_7890_10_28_2014_123456.jpg

I need to replace the middle underscores so that it displays it as date i.e.

AAA_ZZZZ_7890_10-28-2014_123456.jpg

Can some one please suggest a simple update query for this.

The Number of Underscores would be same for all the values in the column but the length will vary for example some can have

AAA_q10WRQ_001_05_15_2014_12.jpg

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2014-02-21 : 16:07:42
Here's one ugly way:

select stuff(stuff(s, idx+3, 1, '-'), idx+6, 1, '-')
from (
select s, patindex('%_[0-1][0-9]_[0-3][0-9]_20[0-2][0-9]_%', s) idx
from (
select 'AAAxZZZZ_7890_10_28_2014_123456.jpg' s union all
select 'AAA_q10WRQ_001_10_28_2014_12.jpg'
) d
) d

output:

AAAxZZZZ_7890_10-28-2014_123456.jpg
AAA_q10WRQ_001_10-28-2014_12.jpg


EDIT:
this version will only work with dates between years 2000 an 2029

Be One with the Optimizer
TG
Go to Top of Page
   

- Advertisement -