T-SQL: Face à la concaténation de chaîne - comment scinde une chaîne en plusieurs enregistrements

Question

Double Possible:
Scinde une chaîne en SQL

J'ai vu un couple de questions liées à la concaténation de chaîne en SQL. Je me demande comment vous vous approchez le problème inverse: le fractionnement du coma chaîne délimitée en lignes de données:

Disons que j'ai des tables:

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

Et souhaitez insérer des données dans la table

userTag(userID,tagID) 'multiple entries per user

Inspiré par des étiquettes Qui ne sont pas dans la base de données? question

MODIFIER

Merci pour les réponses, en fait plus le mérite d'être accepté, mais je ne peux en choisir un, et la solution présentée par Cade Roux avec des récurrences semble assez propre pour moi. Il fonctionne sur SQL Server 2005 et au-dessus.

Pour les versions antérieures de SQL Server, la solution fournie par miies peut être utilisé. Pour travailler avec le type de données texte wcm réponse sera utile. Merci encore.

Answer 1

Il y a une grande variétés de solutions à ce problème documenté ici, y compris ce petit bijou:

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
  )

Answer 2

Vous pouvez également obtenir cet effet à l'aide de XML, comme on le voit ici, ce qui supprime la limitation des réponses qui semblent tous comprennent la récursivité d'une certaine façon. L'utilisation particulière que j'ai fait ici jusqu'à 32 caractères séparateur, mais qui pourrait être augmenté, mais les grandes il ne doit l'être.

create FUNCTION [dbo].[Split] (@sep VARCHAR(32), @s VARCHAR(MAX))
RETURNS TABLE
AS
    RETURN
    (
        SELECT r.value('.','VARCHAR(MAX)') as Item
        FROM (SELECT CONVERT(XML, N'<root><r>' + REPLACE(REPLACE(REPLACE(@s,'& ','&amp; '),'<','&lt;'), @sep, '</r><r>') + '</r></root>') as valxml) x
        CROSS APPLY x.valxml.nodes('//root/r') AS RECORDS(r)
    )

Ensuite, vous pouvez l'appeler à l'aide:

SELECT * FROM dbo.Split(' ', 'I hate bunnies')

Qui renvoie:

-----------
|I        |
|---------|
|hate     |
|---------|
|bunnies  |
-----------

---

Je note, je n'ai pas vraiment de haine lapins... il a juste sauté dans ma tête pour une raison quelconque.

---

Ce qui suit est la chose la plus proche que je pouvais venir avec l'aide de la même méthode dans une fonction table en ligne. NE L'UTILISEZ PAS, C'EST HORRIBLEMENT INEFFICACE! C'est juste ici dans l'intéret de référence.

CREATE FUNCTION [dbo].[Split] (@sep VARCHAR(32), @s VARCHAR(MAX))
RETURNS TABLE
AS
    RETURN
    (
        SELECT r.value('.','VARCHAR(MAX)') as Item
        FROM (SELECT CONVERT(XML, N'<root><r>' + REPLACE(@s, @sep, '</r><r>') + '</r></root>') as valxml) x
        CROSS APPLY x.valxml.nodes('//root/r') AS RECORDS(r)
    )

Answer 3

J'utilise cette fonction (SQL Server 2005 et au-dessus).

create function [dbo].[Split]
(
    @string nvarchar(4000),
    @delimiter nvarchar(10)
)
returns @table table
(
    [Value] nvarchar(4000)
)
begin
    declare @nextString nvarchar(4000)
    declare @pos int, @nextPos int

    set @nextString = ''
    set @string = @string + @delimiter

    set @pos = charindex(@delimiter, @string)
    set @nextPos = 1
    while (@pos <> 0)
    begin
        set @nextString = substring(@string, 1, @pos - 1)

        insert into @table
        (
            [Value]
        )
        values
        (
            @nextString
        )

        set @string = substring(@string, @pos + len(@delimiter), len(@string))
        set @nextPos = @pos
        set @pos = charindex(@delimiter, @string)
    end
    return
end

Answer 4

Pour le cas particulier de fractionnement des chaînes de caractères dans les mots que j'ai trouver une autre solution pour SQL Server 2008.

with testTable AS
(
SELECT 1 AS Id, N'how now brown cow' AS txt UNION ALL
SELECT 2, N'she sells sea shells upon the sea shore' UNION ALL
SELECT 3, N'red lorry yellow lorry' UNION ALL
SELECT 4, N'the quick brown fox jumped over the lazy dog'
)

SELECT display_term, COUNT(*) As Cnt
 FROM testTable
CROSS APPLY sys.dm_fts_parser('"' + txt + '"', 1033, 0,0)
GROUP BY display_term
HAVING COUNT(*) > 1
ORDER BY Cnt DESC

Retourne

display_term                   Cnt
------------------------------ -----------
the                            3
brown                          2
lorry                          2
sea                            2

Answer 5

Légère modification de la solution ci-dessus de sorte qu'il fonctionne avec la longueur variable des délimiteurs.

create FUNCTION dbo.fn_Split2 (@sep nvarchar(10), @s nvarchar(4000))
RETURNS table
AS
RETURN (
    WITH Pieces(pn, start, stop) AS (
      SELECT 1, 1, CHARINDEX(@sep, @s)
      UNION ALL
      SELECT pn + 1, stop + (datalength(@sep)/2), CHARINDEX(@sep, @s, stop + (datalength(@sep)/2))
      FROM Pieces
      WHERE stop > 0
    )
    SELECT pn,
      SUBSTRING(@s, start, CASE WHEN stop > 0 THEN stop-start ELSE 4000 END) AS s
    FROM Pieces
  )

NB: j'ai utilisé datalength() depuis len() signale de manière incorrecte si il y a des espaces.