sql-server - texto - sql server concatenar campos

T-SQL: frente a la concatenación de cadenas: cómo dividir cadenas en múltiples registros

Question

Posible duplicado:
División de cadena en SQL

He visto un par de preguntas relacionadas con la concatenación de cadenas en SQL. Me pregunto cómo abordaría el problema opuesto: dividir la cadena delimitada por coma en filas de datos:

Digamos que tengo tablas:

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

Y quiero insertar datos en la tabla

userTag(userID,tagID) ''multiple entries per user

Inspirado por ¿Qué etiquetas no están en la base de datos? pregunta

EDITAR

Gracias por las respuestas, en realidad más de una merece ser aceptada, pero solo puedo elegir una, y la solución presentada por Cade Roux con las recurrencias me parece bastante limpia. Funciona en SQL Server 2005 y superior.

Para una versión anterior de SQL Server, se puede usar la solución provista por miies . Para trabajar con el tipo de datos de texto, la respuesta de wcm será útil. Gracias de nuevo.

Answer 1

Aquí hay una función de Split que es compatible con las versiones de SQL Server anteriores a 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

Answer 2

Elegí la respuesta de "Nathan Wheeler" porque encontré que la respuesta de "Cade Roux" no funcionaba por encima de un determinado tamaño de cadena.

Un par de puntos

-Me encontré añadiendo la palabra clave DISTINCT me mejoró el rendimiento.

-La respuesta de Nathan solo funciona si tus identificadores tienen 5 caracteres o menos, por supuesto puedes ajustar eso ... Si los elementos que estás dividiendo son identificadores INT como yo lo somos, puedes usar los mismos a continuación:

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

Answer 3

Escribí un momento atrás. Supone que el delimitador es una coma y que los valores individuales no son mayores de 127 caracteres. Podría ser modificado bastante fácilmente.

Tiene la ventaja de no estar limitado a 4.000 caracteres.

¡Buena suerte!

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 

Answer 4

Hay una amplia variedad de soluciones para este problema documentado aquí , incluida esta pequeña joya:

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 5

Leve modificación de la solución anterior para que funcione con delimitadores de longitud 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: He usado datalength () ya que len () informa incorrectamente si hay espacios finales.

Answer 6

Normalmente hago esto con el siguiente código:

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

Puedes probarlo con esta consulta:

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

select * from [dbo].[Split](@example, '';'');

Answer 7

Para el caso particular de dividir cadenas en palabras, he encontrado otra solución para 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

Devoluciones

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

Answer 8

También puede lograr este efecto usando XML, como se ve aquí , que elimina la limitación de las respuestas proporcionadas, que parecen incluir recurrencia de alguna manera. El uso particular que he hecho aquí permite hasta un delimitador de 32 caracteres, pero se puede aumentar por más grande que sea necesario.

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)
    )

Entonces puedes invocarlo usando:

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

Que devuelve:

----------- |I | |---------| |hate | |---------| |bunnies | ----------- Debo señalar que, en realidad, no odio los conejos ... simplemente se me vino a la cabeza por alguna razón. Lo siguiente es lo más parecido que pude encontrar usando el mismo método en una función en línea con valores de tabla. ¡NO LO USE, ES HORRIBLEMENTE INEFICIENTE! Está aquí solo como referencia.

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 9

Usando CLR, aquí hay una alternativa mucho más simple que funciona en todos los casos, pero un 40% más rápida que la respuesta aceptada:

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);
    }
}

Por supuesto, todavía es 8 veces más lento que el regexp_split_to_table de PostgreSQL.

Answer 10

Uso esta función (SQL Server 2005 y superior).

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 11

SELECT substring(commaSeparatedTags,0,charindex('','',commaSeparatedTags))

le dará la primera etiqueta. Puede proceder de manera similar para obtener el segundo y así sucesivamente combinando subcadena y charindex una capa más profunda cada vez. Esa es una solución inmediata, pero solo funciona con muy pocas etiquetas, ya que la consulta crece muy rápidamente y no se puede leer. Pase a las funciones, como se describe en otras respuestas más sofisticadas para esta publicación.