replace in string - 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

replace in string

Author

Topic

slimt_slimt
Aged Yak Warrior

746 Posts

Posted - 2012-07-10 : 12:20:48

hi,

i have a following sample:



create table repl (id varchar(300))
go

insert into repl (id)
select '123,12,some text,5334,234,23' union all
select '124,14,"some text, in quotes, with comma",5334,234,23' union all
select '125,11,even more text,5334,234,23'
go

and this is desired output:
123||12||some text||5334||234||23
124||14||some text, in quotes, with comma||5334||234||23
125||11||even more text||5334||234||23

So i want each comma to be replaces with ||, unless commas are in quotes (e.g.: line 2 of desired output).

thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-07-10 : 12:57:09

for the given sample data, this hack works!


select replace(replace(replace(id,', ','@'),',','||'),'@',', ') from repl

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-07-10 : 13:16:14

Got carried away a bit
Not suggesting you use this but

declare @repl table (id varchar(300))

insert into @repl (id)
select '123,12,some text,5334,234,23' union all
select '124,14,"some text, in quotes, with comma",5334,234,23' union all
select '124,14,"some text, in quotes, with comma","some text, in quotes,,x,,again",5334,234,23' union all
select '125,11,even more text,5334,234,23'

;with cte1 as
(
select id = REPLACE(id,',','\\'), rownum = row_number() over (order by id) from @repl
)
,cte2 as
(
select id, i=CHARINDEX('"',id), j=CHARINDEX('"',id, CHARINDEX('"',id)+1), seq = 1, rownum from cte1
union all
select id, i=CHARINDEX('"',id, j+1), j=CHARINDEX('"',id, CHARINDEX('"',id,j+1)+1), seq = seq + 1, rownum from cte2 where CHARINDEX('"',id, j+1) <> 0
)
, cte3 as
(
select id = replace(cte1.id,SUBSTRING(cte2.id,i,j-i+1), REPLACE(SUBSTRING(cte2.id,i,j-i+1),'\\',',')), cte1.rownum, seq = 2 from cte1 join cte2 on cte1.rownum = cte2.rownum and cte2.seq = 1 and cte2.j <> 0
union all
select id = replace(cte3.id,SUBSTRING(cte2.id,i,j-i+1), REPLACE(SUBSTRING(cte2.id,i,j-i+1),'\\',',')), cte1.rownum, seq = cte3.seq + 1 from cte1 join cte2 on cte1.rownum = cte2.rownum join cte3 on cte3.rownum = cte2.rownum and cte2.seq = cte3.seq and cte2.j <> 0
)
select id from cte3 where seq = (select MAX(seq) from cte3 c3 where cte3.rownum = c3.rownum)
union all
select id from cte1 where id not like '%"%'

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources