CODEWITHSUNDEEP
sqlsql-serverreplace
Justin Whitehead
Justin Whitehead

Reputation: 65

SQL replace every other comma with a semicolon

I have a bunch of strings that should have been stored as value pairs but were not. Now I need to replace every other comma with a semicolon to make them pairs. Hoping to find a simple way of doing this, but there might not be one.

ex:

-1328.89,6354.22,-1283.94,6242.96,-1172.68,6287.91,-1217.63,6399.18

should be:

-1328.89,6354.22;-1283.94,6242.96;-1172.68,6287.91;-1217.63,6399.18

Upvotes: 2

Views: 1467

Answers (4)

Anon
Anon

Reputation: 10908

This can be done in a single query:

DECLARE @t TABLE (id int, col varchar(max))
INSERT @t VALUES
(1,'-1328.89,6354.22,-1283.94,6242.96,-1172.68,6287.91,-1217.63,6399.18'),
(2,'-4534.89,454.22,-1123.94,2932.96,-1872.68,327.91,-417.63,635.18')

;WITH t AS (
  SELECT id, i % 2 x, i / 2 y, val
  FROM @t
  CROSS APPLY (SELECT CAST('<a>'+REPLACE(col,',','</a><a>')+'</a>' AS xml) xml1 ) t1
  CROSS APPLY (
    SELECT
      n.value('for $i in . return count(../*[. << $i])', 'int') i,
      n.value('.','varchar(max)') AS val
    FROM xml1.nodes('a') x(n)
  ) t2
)
SELECT id, y, [0]+','+[1] col
FROM t
PIVOT(MAX([val]) FOR x IN ([0],[1])) t3
ORDER BY id, y


id  y   val
----------------------------
1   0   -1328.89,6354.22
1   1   -1283.94,6242.96
1   2   -1172.68,6287.91
1   3   -1217.63,6399.18
2   0   -4534.89,454.22
2   1   -1123.94,2932.96
2   2   -1872.68,327.91
2   3   -417.63,635.18

Upvotes: 0

Ron Smith
Ron Smith

Reputation: 3266

This can be done with a combination of dynamic sql and for xml:

declare @sql nvarchar(max)
set @sql = '-1328.89,6354.22,-1283.94,6242.96,-1172.68,6287.91,-1217.63,6399.18'
set @sql = '
    select replace((select cast(value as varchar(50)) +
    cast(case row_number() over(order by sort)%2 when 0 then '','' else '';'' end as char(1))
    from (select ' + replace(@sql,',',' value,1 sort union all select ') + ',1 sort)q
    for xml path(''''))+''||'','',||'','''') YourUpdatedValue'
exec(@sql)

Upvotes: 0

t-clausen.dk
t-clausen.dk

Reputation: 44326

create function f_tst(@a varchar(100)) -- use right size of field
returns varchar(100) -- make sure you use the right size of field
begin
declare @pos int = charindex(',', @a) + 1
;while 0 < charindex(',', @a, @pos) 
select @a = stuff(@a, charindex(',', @a, @pos), 1, ';'), 
@pos = charindex(',', @a, charindex(',', @a, @pos + 1)) + 1

return @a
end

go

declare @a varchar(100) = '-1328.89,6354.22,-1283.94,6242.96,-1172.68,6287.91,-1217.63,6399.18'
select dbo.f_tst(@a)

Or in your example

update <table>
set <field> = dbo.f_tst(<field>)

Upvotes: 1

thepirat000
thepirat000

Reputation: 13114

Surely not so simple as you want, but a CHARINDEX/SUBSTRING solution:

Declare @input nvarchar(max) = '-1328.89,6354.22,-1283.94,6242.96,-1172.68,6287.91,-1217.63,6399.18'

Declare @i int = 0, @t int = 0, @isComma bit = 1
Declare @output nvarchar(max) = ''
Select @i = CHARINDEX(',', @input)
While (@i > 0)
Begin
    Select @output = @output + SUBSTRING(@input, @t + 1, @i - @t - 1) + CASE @isComma WHEN 1 THEN ',' ELSE ';' END
    Select @t = @i
    Select @i = CHARINDEX(',', @input, @i + 1), @isComma = 1 - @isComma
End
Select @output = @output + SUBSTRING(@input, @t + 1, 1000)
Select @output

Upvotes: 0

Related Questions