T-SQL: opposé à la concaténation de chaîne de caractères - comment diviser la chaîne de caractères en plusieurs enregistrements [dupliquer]

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 aborder le problème inverse: diviser la chaîne délimitée par coma en rangées de données:

disons que j'ai des tables:

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

Et veulent insérer des données dans le tableau

userTag(userID,tagID) 'multiple entries per user

inspiré par quelles étiquettes ne sont pas dans la base de données? question

MODIFIER

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

pour la version antérieure de SQL Server la solution fournie par miies peut être utilisée. Pour travailler avec les données de texte type WCM réponse sera utile. Merci encore.

135
demandé sur Community 2008-11-24 20:17:56

11 réponses

il existe une grande variété de solutions à ce problème documentée 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
  )
147
répondu Cade Roux 2008-11-24 18:01:53

vous pouvez également obtenir cet effet en utilisant XML, comme vu ici , qui supprime la limitation des réponses fournies qui semblent toutes inclure la récursion d'une certaine manière. L'usage particulier que j'ai fait ici permet jusqu'à un délimiteur de 32 caractères, mais cela pourrait être augmenté quelle que soit la taille qu'il doit ê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)
    )

alors vous pouvez l'invoquer en utilisant:

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 pourrais trouver en utilisant la même méthode dans une fonction de valeur de table en ligne. NE L'UTILISEZ PAS, C'EST HORRIBLEMENT INEFFICACE! Il est juste là pour 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)
    )
83
répondu Nathan Wheeler 2014-03-27 14:53:43

j'utilise cette fonction (SQL Server 2005 et ci-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
19
répondu user39603 2010-10-21 14:37:04

pour le cas particulier de séparer les chaînes en mots, j'ai rencontré 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
11
répondu Martin Smith 2010-07-31 12:18:37

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

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() car len() ne signale pas correctement s'il y a des espaces de fuite.

7
répondu Rory 2017-05-23 12:34:43

Voici une fonction Split compatible avec les versions SQL Server antérieures à 2005.

CREATE FUNCTION dbo.Split(@data nvarchar(4000), @delimiter nvarchar(100))  
RETURNS @result table (Id int identity(1,1), Data nvarchar(4000)) 
AS  
BEGIN 
    DECLARE @pos   INT
    DECLARE @start INT
    DECLARE @len   INT
    DECLARE @end   INT

    SET @len   = LEN('.' + @delimiter + '.') - 2
    SET @end   = LEN(@data) + 1
    SET @start = 1
    SET @pos   = 0

    WHILE (@pos < @end)
    BEGIN
        SET @pos = CHARINDEX(@delimiter, @data, @start)
        IF (@pos = 0) SET @pos = @end

        INSERT @result (data) SELECT SUBSTRING(@data, @start, @pos - @start)
        SET @start = @pos + @len
    END

    RETURN
END
7
répondu Tomalak 2011-03-24 17:38:29

en utilisant CLR, voici une alternative beaucoup plus simple qui fonctionne dans tous les cas, mais 40% plus rapide que la réponse acceptée:

using System;
using System.Collections;
using System.Data.SqlTypes;
using System.Text.RegularExpressions;
using Microsoft.SqlServer.Server;

public class UDF
{
    [SqlFunction(FillRowMethodName="FillRow")]
    public static IEnumerable RegexSplit(SqlString s, SqlString delimiter)
    {
        return Regex.Split(s.Value, delimiter.Value);
    }

    public static void FillRow(object row, out SqlString str)
    {
        str = new SqlString((string) row);
    }
}

bien sûr, il est encore 8 fois plus lent que le regexp_split_to_table de PostgreSQL .

7
répondu sayap 2011-07-22 10:37:37
SELECT substring(commaSeparatedTags,0,charindex(',',commaSeparatedTags))

vous donnera la première balise. Vous pouvez procéder de la même manière pour obtenir le second et ainsi de suite en combinant la sous-couche et le charindex une couche plus profonde à chaque fois. C'est une solution immédiate, mais cela ne fonctionne qu'avec très peu de balises car la requête croît très rapidement en taille et devient illisible. Passez aux fonctions puis, comme indiqué dans d'autres, plus sophistiqué des réponses à ce poste.

5
répondu Yann Semet 2008-11-24 17:53:15

j'ai écrit ça il y a longtemps. Il suppose que le délimiteur est une virgule et que les valeurs individuelles ne sont pas plus grandes que 127 caractères. Il peut être modifié assez facilement.

Il a l'avantage de ne pas être limité à 4000 caractères.

bonne chance!

ALTER Function [dbo].[SplitStr] ( 
        @txt text 
) 
Returns @tmp Table 
        ( 
                value varchar(127)
        ) 
as 
BEGIN 
        declare @str varchar(8000) 
                , @Beg int 
                , @last int 
                , @size int 

        set @size=datalength(@txt) 
        set @Beg=1 


        set @str=substring(@txt,@Beg,8000) 
        IF len(@str)<8000 set @Beg=@size 
        ELSE BEGIN 
                set @last=charindex(',', reverse(@str)) 
                set @str=substring(@txt,@Beg,8000-@last) 
                set @Beg=@Beg+8000-@last+1 
        END 

        declare @workingString varchar(25) 
                , @stringindex int 



        while @Beg<=@size Begin 
                WHILE LEN(@str) > 0 BEGIN 
                        SELECT @StringIndex = CHARINDEX(',', @str) 

                        SELECT 
                                @workingString = CASE 
                                        WHEN @StringIndex > 0 THEN SUBSTRING(@str, 1, @StringIndex-1) 
                                        ELSE @str 
                                END 

                        INSERT INTO 
                                @tmp(value)
                        VALUES 
                                (cast(rtrim(ltrim(@workingString)) as varchar(127)))
                        SELECT @str = CASE 
                                WHEN CHARINDEX(',', @str) > 0 THEN SUBSTRING(@str, @StringIndex+1, LEN(@str)) 
                                ELSE '' 
                        END 
                END 
                set @str=substring(@txt,@Beg,8000) 

                if @Beg=@size set @Beg=@Beg+1 
                else IF len(@str)<8000 set @Beg=@size 
                ELSE BEGIN 
                        set @last=charindex(',', reverse(@str)) 
                        set @str=substring(@txt,@Beg,8000-@last) 
                        set @Beg=@Beg+8000-@last+1 

                END 
        END     

        return
END 
2
répondu wcm 2008-11-24 17:35:20

J'ai voté" Nathan Wheeler "réponse comme j'ai trouvé" Cade Roux " réponse ne fonctionne pas au-dessus d'une certaine taille de chaîne.

Couple de points

- j'ai trouvé l'ajout du mot-clé DISTINCT amélioration de la performance pour moi.

- la réponse de Nathan ne fonctionne que si vos identificateurs sont de 5 caractères ou moins, bien sûr vous pouvez ajuster cela...Si les articles que vous divisez sont INT identificateurs que je suis, vous pouvez nous même que moi ci-dessous:

CREATE FUNCTION [dbo].Split
(
    @sep VARCHAR(32), 
    @s VARCHAR(MAX)
)
RETURNS 
    @result TABLE (
        Id INT NULL
    )   
AS
BEGIN
    DECLARE @xml XML
    SET @XML = N'<root><r>' + REPLACE(@s, @sep, '</r><r>') + '</r></root>'

    INSERT INTO @result(Id)
    SELECT DISTINCT r.value('.','int') as Item
    FROM @xml.nodes('//root//r') AS RECORDS(r)

    RETURN
END
2
répondu Darren 2012-05-22 11:13:40

je le fais habituellement avec le code suivant:

create function [dbo].[Split](@string varchar(max), @separator varchar(10))
returns @splited table ( stringPart varchar(max) )
with execute as caller
as
begin
    declare @stringPart varchar(max);
    set @stringPart = '';

    while charindex(@separator, @string) > 0
    begin
        set @stringPart = substring(@string, 0, charindex(@separator, @string));
        insert into @splited (stringPart) values (@stringPart);
        set @string = substring(@string, charindex(@separator, @string) + len(@separator), len(@string) + 1);
    end

    return;
end
go

Vous pouvez le tester avec cette requête:

declare @example varchar(max);
set @example = 'one;string;to;rule;them;all;;';

select * from [dbo].[Split](@example, ';');
0
répondu Marek 2012-04-24 13:15:08