TA貢獻(xiàn)1871條經(jīng)驗(yàn) 獲得超8個(gè)贊
我之前使用過這個(gè)SQL可能對(duì)你有用： -
CREATE FUNCTION dbo.splitstring ( @stringToSplit VARCHAR(MAX) )RETURNS @returnList TABLE ([Name] [nvarchar] (500))ASBEGIN
DECLARE @name NVARCHAR(255)
DECLARE @pos INT WHILE CHARINDEX(',', @stringToSplit) > 0
BEGIN
SELECT @pos = CHARINDEX(',', @stringToSplit)
SELECT @name = SUBSTRING(@stringToSplit, 1, @pos-1)
INSERT INTO @returnList
SELECT @name SELECT @stringToSplit = SUBSTRING(@stringToSplit, @pos+1, LEN(@stringToSplit)-@pos)
END
INSERT INTO @returnList SELECT @stringToSplit RETURNEND并使用它： -
SELECT * FROM dbo.splitstring('91,12,65,78,56,789')
TA貢獻(xiàn)1780條經(jīng)驗(yàn) 獲得超4個(gè)贊
最簡單的方法是使用XML格式。
1.將字符串轉(zhuǎn)換為不帶表的行
QUERY
DECLARE @String varchar(100) = 'String1,String2,String3'
-- To change ',' to any other delimeter, just change ',' to your desired one
DECLARE @Delimiter CHAR = ','
SELECT LTRIM(RTRIM(Split.a.value('.', 'VARCHAR(100)'))) 'Value'
FROM
(
SELECT CAST ('<M>' + REPLACE(@String, @Delimiter, '</M><M>') + '</M>' AS XML) AS Data
) AS A
CROSS APPLY Data.nodes ('/M') AS Split(a)
結(jié)果
x---------x
| Value |
x---------x
| String1 |
| String2 |
| String3 |
x---------x
2.從表中轉(zhuǎn)換為具有每個(gè)CSV行的ID的行
消息表
x-----x--------------------------x
| Id | Value |
x-----x--------------------------x
| 1 | String1,String2,String3 |
| 2 | String4,String5,String6 |
x-----x--------------------------x
QUERY
-- To change ',' to any other delimeter, just change ',' before '</M><M>' to your desired one
DECLARE @Delimiter CHAR = ','
SELECT ID,LTRIM(RTRIM(Split.a.value('.', 'VARCHAR(100)'))) 'Value'
FROM
(
SELECT ID,CAST ('<M>' + REPLACE(VALUE, @Delimiter, '</M><M>') + '</M>' AS XML) AS Data
FROM TABLENAME
) AS A
CROSS APPLY Data.nodes ('/M') AS Split(a)
結(jié)果
x-----x----------x
| Id | Value |
x-----x----------x
| 1 | String1 |
| 1 | String2 |
| 1 | String3 |
| 2 | String4 |
| 2 | String5 |
| 2 | String6 |
x-----x----------x
舉報(bào)