CODEWITHSUNDEEP
sqlsql-serverregexstring
user3162968
user3162968

Reputation: 1046

SQL, get adhering pairs of values from comma separated string

I have the following string (example) in one cell of a column:

 1,4,3,8,23,7

I need to get pairs of values as below into a new table:

(1,4)
  (4,3)
    (3,8)
      (8,23)
        (23,7)

I hope that I explained what I need properly, so that you can understand me :) I apprecciate even one sentence answer because I like to solve programming problems myself :)

Upvotes: 1

Views: 206

Answers (3)

Andrey Morozov
Andrey Morozov

Reputation: 7989

Another solution without using XML - clear set based approach using recursive CTE.

create table #tmp (value varchar(100));
insert into #tmp values ('1,4,3,8,23,7');


with r as (
    select value, cast(null as varchar(100)) [x], 0 [no] from #tmp
    union all
    select right(value, len(value)-case charindex(',', value) when 0 then len(value) else charindex(',', value) end) [value]
    , left(r.[value], case charindex(',', r.value) when 0 then len(r.value) else abs(charindex(',', r.[value])-1) end ) [x]
    , [no] + 1 [no]
    from r where value > '')

select '(' + cast(s1.[x] as varchar(10)) +', '+ cast(s2.[x] as varchar(10)) + ')'
from r as s1
join r as s2 on s1.[no] + 1 = s2.[no]
where s1.x is not null;


drop table #tmp;

Output:

result
---------
(1, 4)
(4, 3)
(3, 8)
(8, 23)
(23, 7)

Upvotes: 1

dean
dean

Reputation: 10098

Sorry to spoil the fun :)

declare 
    @col_list varchar(1000),
    @sep char(1)

set @col_list = '1,4,3,8,23,7'
set @sep = ','

;with x as (
select substring(@col_list, n, charindex(@sep, @col_list + @sep, n) - n) as col,
row_number() over(order by n) as r
from numbers where substring(@sep + @col_list, n, 1) = @sep
and n < len(@col_list) + 1
)
select x2.col, x1.col
from x as x1
inner join x as x2 on x1.r = x2.r+1

http://dataeducation.com/you-require-a-numbers-table/

Upvotes: 1

t-clausen.dk
t-clausen.dk

Reputation: 44346

DECLARE @data varchar(2000) = '1,4,3,8,23,7'

;WITH x as
(
     SELECT t.c.value('.', 'VARCHAR(2000)') v, row_number() over (order by (select 1)) rn
      FROM (
         SELECT x = CAST('<t>' + 
               REPLACE(@data, ',', '</t><t>') + '</t>' AS XML)
     ) a
     CROSS APPLY x.nodes('/t') t(c)
)
SELECT t1.v, t2.v
FROM x t1
JOIN x t2
on t1.rn = t2.rn - 1

Result:

1   4
4   3
3   8
8   23
23  7

Upvotes: 2

Related Questions