T-SQL:与字符串连接相反-如何将字符串拆分为多个记录
我想知道您将如何解决相反的问题:将逗号分隔的字符串拆分为数据行:可以说我有表:
userTypedTags(userID,commaSeparatedTags) 'one entry per user
tags(tagID,name)
并想将数据插入表中
userTag(userID,tagID) 'multiple entries per user
解决方案:
这个问题的解决方案记录在这里,包括这个小gem:
CREATE FUNCTION dbo.Split (@sep char(1), @s varchar(512))
RETURNS table
AS
RETURN (
WITH Pieces(pn, start, stop) AS (
SELECT 1, 1, CHARINDEX(@sep, @s)
UNION ALL
SELECT pn + 1, stop + 1, CHARINDEX(@sep, @s, stop + 1)
FROM Pieces
WHERE stop > 0
)
SELECT pn,
SUBSTRING(@s, start, CASE WHEN stop > 0 THEN stop-start ELSE 512 END) AS s
FROM Pieces
)
页:
[1]