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

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é tags 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.

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 ) 

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 boost 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'' + REPLACE(REPLACE(REPLACE(@s,'& ','& '),'< ','<'), @sep, '') + '') 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'' + REPLACE(@s, @sep, '') + '') as valxml) x CROSS APPLY x.valxml.nodes('//root/r') AS RECORDS(r) ) 

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 

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 

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.

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 

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.

 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 tags, 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.

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 

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'' + REPLACE(@s, @sep, '') + '' INSERT INTO @result(Id) SELECT DISTINCT r.value('.','int') as Item FROM @xml.nodes('//root//r') AS RECORDS(r) RETURN END 

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, ';');