玩玩SQL~將字串裡面的逗號轉換成資料行顯示

sql

純真的人 2024-08-08 15:37:52 ‧ 991 瀏覽

分享至

最近可以閒一下發個SQL小測試~看大家如何寫SQL

基本上這題有很多SQL寫法啦

提供基本資料

declare @Str varchar(max) = '-22,55,66,11,-44'

轉換如圖

在還沒有支援STRING_SPLIT版本之前，可以用這方式將逗號轉成多行

declare @Str varchar(max) = '-22,55,66,11,-44'
declare @x xml = '<row>' + REPLACE(@Str, ',','</row><row>')+ '</row>'

select ROW_NUMBER() over(order by (select 1)) as Sort
,item.c.value('.', 'int') AS list
from @x.nodes('*') item (c)

--或者

declare @Str varchar(max) = '-22,55,66,11,-44'

select ROW_NUMBER() over(order by (select 1)) as Sort
,list
from (
	select CONVERT(XML, '<n>' + replace(@Str, ',', '</n><n>') + '</n>') Str
) k
outer apply (
	select item.c.value('.', 'int') AS list
	from k.Str.nodes('*') item (c)
) s

--或者

declare @Tmp table(
	Str nvarchar(max)
)
insert into @Tmp
values('-22,55,66,11,-44')

select ROW_NUMBER() over(order by (select 1)) as Sort
,list
from (
	select CONVERT(XML, '<n>' + replace(Str, ',', '</n><n>') + '</n>') Str
	from @Tmp
) k
outer apply (
	select item.c.value('.', 'int') AS list
	from k.Str.nodes('*') item (c)
) s

登入發表討論

直播研討會

{{ item.channelVendor }} {{ item.webinarstarted }} |

直播中

2 個回答

pilipala

iT邦研究生 5 級 ‧ 2024-08-09 02:29:59

最佳解答

MS SQL 2016 開始有 string_split() 可以使用

declare @Str varchar(max) = '-22,55,66,11,-44'

SELECT * from string_split(@Str , ',' , 1)

回應 1
分享
檢舉

純真的人 iT邦大師 1 級 ‧ 2024-08-09 08:27:42 檢舉

對呀~想到以前就是只有複雜的寫法

登入發表回應

alien663

iT邦研究生 4 級 ‧ 2024-08-09 08:14:06

建立資料

select *
into #temp
from (
    values
        (1, '-22,55,66,11,-44'),
        (2, 'A,B,C,D,E,F'),
        (3, 'G,H,I,J')
) as T(id, s)

從table拆出來

select * 
from #temp T
    CROSS APPLY string_split(T.s, ',')

再把它組回去

WITH temp as (
    select * 
    from #temp T
        CROSS APPLY string_split(T.s, ',')
)
select T1.id, 
    stuff(
        (
            select ',' + T2.[value]
            from temp T2 where t1.id = t2.id for xml path('')
        ), 1, 1, ''
    )
from temp T1
group by t1.id

回應 2
分享
檢舉

純真的人 iT邦大師 1 級 ‧ 2024-08-09 08:29:02 檢舉

哈~也是有用到最新的string_split

純真的人 iT邦大師 1 級 ‧ 2024-08-09 09:42:36 檢舉

你的簡化一下~應該可以這樣吧

declare @Tmp table(
	ID int
	,Str nvarchar(max)
)
insert into @Tmp
values (1, '-22,55,66,11,-44'),
(2, 'A,B,C,D,E,F'),
(3, 'G,H,I,J')

select ordinal Sort
,ID
,value list
from @Tmp k
outer apply string_split(k.Str, ',',1)

參考~
https://dbfiddle.uk/HHErV8vX

登入發表回應