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.