T-SQL:与字符串连接相反 - 如何将字符串拆分为多条记录 [英] T-SQL: Opposite to string concatenation - how to split string into multiple records

问题描述

可能的重复:
在 SQL 中拆分字符串

我在 SQL 中看到了几个与字符串连接相关的问题.我想知道您将如何解决相反的问题:将逗号分隔的字符串拆分为数据行:

I have seen a couple of questions related to string concatenation in SQL. I wonder how would you approach the opposite problem: splitting coma delimited string into rows of data:

假设我有桌子:

userTypedTags(userID,commaSeparatedTags) 'one entry per user
tags(tagID,name)

想往表中插入数据

userTag(userID,tagID) 'multiple entries per user

灵感来自哪些标签不在数据库中?问题

编辑

感谢您的回答，实际上值得接受的不止一个，但我只能选择一个，Cade Roux 提出的带有递归的解决方案 对我来说似乎很干净.它适用于 SQL Server 2005 及更高版本.

Thanks for the answers, actually more then one deserves to be accepted but I can only pick one, and the solution presented by Cade Roux with recursions seems pretty clean to me. It works on SQL Server 2005 and above.

对于早期版本的 SQL Server，解决方案 由miies提供.用于处理文本数据类型 wcm answer 会有所帮助.再次感谢.

For earlier version of SQL Server the solution provided by miies can be used. For working with text data type wcm answer will be helpful. Thanks again.

推荐答案

这个问题有很多种解决方案 此处记录，包括这个小宝石:

There are a wide varieties of solutions to this problem documented here, including this little 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
  )

这篇关于T-SQL:与字符串连接相反 - 如何将字符串拆分为多条记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！