string_split separar por name invalid into comas columns cadena sql-server tsql split comma

sql-server - separar - sql server split string into rows



Convertir una cadena separada por comas en filas individuales (11)

Tengo una tabla SQL como esta:

| SomeID | OtherID | Data +----------------+-------------+------------------- | abcdef-..... | cdef123-... | 18,20,22 | abcdef-..... | 4554a24-... | 17,19 | 987654-..... | 12324a2-... | 13,19,20

¿Hay una consulta donde puedo realizar una consulta como SELECT OtherID, SplitData WHERE SomeID = ''abcdef-.......'' que devuelve filas individuales, como esta:

| OtherID | SplitData +-------------+------------------- | cdef123-... | 18 | cdef123-... | 20 | cdef123-... | 22 | 4554a24-... | 17 | 4554a24-... | 19

Básicamente dividir mis datos en la coma en filas individuales?

Soy consciente de que almacenar una cadena comma-separated en una base de datos relacional suena tonto, pero el caso de uso normal en la aplicación para el consumidor lo hace realmente útil.

No quiero hacer la división en la aplicación ya que necesito paginación, por lo que quería explorar las opciones antes de refacturar toda la aplicación.

Es SQL Server 2008 (no R2).


Al usar este enfoque, debes asegurarte de que ninguno de tus valores contenga algo que sería XML ilegal - user1151923

Siempre uso el método XML. Asegúrate de usar XML VÁLIDO. Tengo dos funciones para convertir XML y texto válidos. (Tiendo a quitar los retornos de carro ya que generalmente no los necesito).

CREATE FUNCTION dbo.udf_ConvertTextToXML (@Text varchar(MAX)) RETURNS varchar(MAX) AS BEGIN SET @Text = REPLACE(@Text,CHAR(10),'''') SET @Text = REPLACE(@Text,CHAR(13),'''') SET @Text = REPLACE(@Text,''<'',''&lt;'') SET @Text = REPLACE(@Text,''&'',''&amp;'') SET @Text = REPLACE(@Text,''>'',''&gt;'') SET @Text = REPLACE(@Text,'''''''',''&apos;'') SET @Text = REPLACE(@Text,''"'',''&quot;'') RETURN @Text END CREATE FUNCTION dbo.udf_ConvertTextFromXML (@Text VARCHAR(MAX)) RETURNS VARCHAR(max) AS BEGIN SET @Text = REPLACE(@Text,''&lt;'',''<'') SET @Text = REPLACE(@Text,''&amp;'',''&'') SET @Text = REPLACE(@Text,''&gt;'',''>'') SET @Text = REPLACE(@Text,''&apos;'','''''''') SET @Text = REPLACE(@Text,''&quot;'',''"'') RETURN @Text END


A continuación funciona en el servidor sql 2008

select *, ROW_NUMBER() OVER(order by items) as row# from ( select 134 myColumn1, 34 myColumn2, ''d,c,k,e,f,g,h,a'' comaSeperatedColumn) myTable cross apply SPLIT (rtrim(comaSeperatedColumn), '','') splitedTable -- gives ''items'' column

Obtendrá todo el producto cartesiano con las columnas de la tabla de origen más los "elementos" de la tabla dividida.


A partir de febrero de 2016 - ver el ejemplo de la tabla TALLY - es muy probable que supere a mi TVF a continuación, desde febrero de 2014. Mantener la publicación original a continuación para la posteridad:

Demasiado código repetido para mi gusto en los ejemplos anteriores. Y no me gusta el rendimiento de CTE y XML. Además, una Id explícita para que los consumidores que son específicos de la orden puedan especificar una cláusula ORDER BY .

CREATE FUNCTION dbo.Split ( @Line nvarchar(MAX), @SplitOn nvarchar(5) = '','' ) RETURNS @RtnValue table ( Id INT NOT NULL IDENTITY(1,1) PRIMARY KEY CLUSTERED, Data nvarchar(100) NOT NULL ) AS BEGIN IF @Line IS NULL RETURN DECLARE @split_on_len INT = LEN(@SplitOn) DECLARE @start_at INT = 1 DECLARE @end_at INT DECLARE @data_len INT WHILE 1=1 BEGIN SET @end_at = CHARINDEX(@SplitOn,@Line,@start_at) SET @data_len = CASE @end_at WHEN 0 THEN LEN(@Line) ELSE @end_at-@start_at END INSERT INTO @RtnValue (data) VALUES( SUBSTRING(@Line,@start_at,@data_len) ); IF @end_at = 0 BREAK; SET @start_at = @end_at + @split_on_len END RETURN END


Es bueno ver que se ha resuelto en la versión 2016, pero para todos aquellos que no lo son, aquí hay dos versiones generalizadas y simplificadas de los métodos anteriores.

El método XML es más corto, pero por supuesto requiere que la cadena permita el xml-trick (no caracteres "malos").

Método XML:

create function dbo.splitString(@input Varchar(max), @Splitter VarChar(99)) returns table as Return SELECT Split.a.value(''.'', ''VARCHAR(max)'') AS Data FROM ( SELECT CAST (''<M>'' + REPLACE(@input, @Splitter, ''</M><M>'') + ''</M>'' AS XML) AS Data ) AS A CROSS APPLY Data.nodes (''/M'') AS Split(a);

Método recursivo:

create function dbo.splitString(@input Varchar(max), @Splitter Varchar(99)) returns table as Return with tmp (DataItem, ix) as ( select @input , CHARINDEX('''',@Input) --Recu. start, ignored val to get the types right union all select Substring(@input, ix+1,ix2-ix-1), ix2 from (Select *, CHARINDEX(@Splitter,@Input+@Splitter,ix+1) ix2 from tmp) x where ix2<>0 ) select DataItem from tmp where ix<>0

Función en acción

Create table TEST_X (A int, CSV Varchar(100)); Insert into test_x select 1, ''A,B''; Insert into test_x select 2, ''C,D''; Select A,data from TEST_X x cross apply dbo.splitString(x.CSV,'','') Y; Drop table TEST_X

XML-METHOD 2: Unicode Friendly 😀 (Adición cortesía de Max Hodges) create function dbo.splitString(@input nVarchar(max), @Splitter nVarchar(99)) returns table as Return SELECT Split.a.value(''.'', ''NVARCHAR(max)'') AS Data FROM ( SELECT CAST (''<M>'' + REPLACE(@input, @Splitter, ''</M><M>'') + ''</M>'' AS XML) AS Data ) AS A CROSS APPLY Data.nodes (''/M'') AS Split(a);


Finalmente, la espera ha terminado con SQL Server 2016 . Han introducido la función de cadena dividida, STRING_SPLIT :

select OtherID, cs.Value --SplitData from yourtable cross apply STRING_SPLIT (Data, '','') cs

Todos los otros métodos para dividir cadenas como XML, tabla Tally, while loop, etc. han sido volados por esta función STRING_SPLIT .

Aquí hay un excelente artículo con comparación de rendimiento: Sorpresas y suposiciones de rendimiento: STRING_SPLIT .

Para versiones anteriores, usar la tabla de conteo aquí es una función de secuencia dividida (el mejor enfoque posible)

CREATE FUNCTION [dbo].[DelimitedSplit8K] (@pString VARCHAR(8000), @pDelimiter CHAR(1)) RETURNS TABLE WITH SCHEMABINDING AS RETURN --===== "Inline" CTE Driven "Tally Table" produces values from 0 up to 10,000... -- enough to cover NVARCHAR(4000) WITH E1(N) AS ( SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 ), --10E+1 or 10 rows E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max cteTally(N) AS (--==== This provides the "base" CTE and limits the number of rows right up front -- for both a performance gain and prevention of accidental "overruns" SELECT TOP (ISNULL(DATALENGTH(@pString),0)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4 ), cteStart(N1) AS (--==== This returns N+1 (starting position of each "element" just once for each delimiter) SELECT 1 UNION ALL SELECT t.N+1 FROM cteTally t WHERE SUBSTRING(@pString,t.N,1) = @pDelimiter ), cteLen(N1,L1) AS(--==== Return start and length (for use in substring) SELECT s.N1, ISNULL(NULLIF(CHARINDEX(@pDelimiter,@pString,s.N1),0)-s.N1,8000) FROM cteStart s ) --===== Do the actual split. The ISNULL/NULLIF combo handles the length for the final element when no delimiter is found. SELECT ItemNumber = ROW_NUMBER() OVER(ORDER BY l.N1), Item = SUBSTRING(@pString, l.N1, l.L1) FROM cteLen l ;

Referido de Tally OH! Una función mejorada de SQL 8K "Divisor CSV"


Función

CREATE FUNCTION dbo.SplitToRows (@column varchar(100), @separator varchar(10)) RETURNS @rtnTable TABLE ( ID int identity(1,1), ColumnA varchar(max) ) AS BEGIN DECLARE @position int = 0 DECLARE @endAt int = 0 DECLARE @tempString varchar(100) set @column = ltrim(rtrim(@column)) WHILE @position<=len(@column) BEGIN set @endAt = CHARINDEX(@separator,@column,@position) if(@endAt=0) begin Insert into @rtnTable(ColumnA) Select substring(@column,@position,len(@column)-@position) break; end set @tempString = substring(ltrim(rtrim(@column)),@position,@endAt-@position) Insert into @rtnTable(ColumnA) select @tempString set @position=@endAt+1; END return END

Caso de uso

select * from dbo.SplitToRows(''T14; p226.0001; eee; 3554;'', '';'')

O solo una selección con un conjunto de resultados múltiples

DECLARE @column varchar(max)= ''1234; 4748;abcde; 324432'' DECLARE @separator varchar(10) = '';'' DECLARE @position int = 0 DECLARE @endAt int = 0 DECLARE @tempString varchar(100) set @column = ltrim(rtrim(@column)) WHILE @position<=len(@column) BEGIN set @endAt = CHARINDEX(@separator,@column,@position) if(@endAt=0) begin Select substring(@column,@position,len(@column)-@position) break; end set @tempString = substring(ltrim(rtrim(@column)),@position,@endAt-@position) select @tempString set @position=@endAt+1; END


Mira esto

SELECT A.OtherID, Split.a.value(''.'', ''VARCHAR(100)'') AS Data FROM ( SELECT OtherID, CAST (''<M>'' + REPLACE(Data, '','', ''</M><M>'') + ''</M>'' AS XML) AS Data FROM Table1 ) AS A CROSS APPLY Data.nodes (''/M'') AS Split(a);


Puede usar las maravillosas funciones recursivas de SQL Server:

Tabla de muestra:

CREATE TABLE Testdata ( SomeID INT, OtherID INT, String VARCHAR(MAX) ) INSERT Testdata SELECT 1, 9, ''18,20,22'' INSERT Testdata SELECT 2, 8, ''17,19'' INSERT Testdata SELECT 3, 7, ''13,19,20'' INSERT Testdata SELECT 4, 6, '''' INSERT Testdata SELECT 9, 11, ''1,2,3,4''

La consulta

;WITH tmp(SomeID, OtherID, DataItem, String) AS ( SELECT SomeID, OtherID, LEFT(String, CHARINDEX('','', String + '','') - 1), STUFF(String, 1, CHARINDEX('','', String + '',''), '''') FROM Testdata UNION all SELECT SomeID, OtherID, LEFT(String, CHARINDEX('','', String + '','') - 1), STUFF(String, 1, CHARINDEX('','', String + '',''), '''') FROM tmp WHERE String > '''' ) SELECT SomeID, OtherID, DataItem FROM tmp ORDER BY SomeID -- OPTION (maxrecursion 0) -- normally recursion is limited to 100. If you know you have very long -- strings, uncomment the option

Salida

SomeID | OtherID | DataItem --------+---------+---------- 1 | 9 | 18 1 | 9 | 20 1 | 9 | 22 2 | 8 | 17 2 | 8 | 19 3 | 7 | 13 3 | 7 | 19 3 | 7 | 20 4 | 6 | 9 | 11 | 1 9 | 11 | 2 9 | 11 | 3 9 | 11 | 4


;WITH tmp(SomeID, OtherID, DataItem, Data) as ( SELECT SomeID, OtherID, LEFT(Data, CHARINDEX('','',Data+'','')-1), STUFF(Data, 1, CHARINDEX('','',Data+'',''), '''') FROM Testdata WHERE Data > '''' ) SELECT SomeID, OtherID, Data FROM tmp ORDER BY SomeID

con solo una pequeña modificación a la consulta anterior ...


DECLARE @id_list VARCHAR(MAX) = ''1234,23,56,576,1231,567,122,87876,57553,1216'' DECLARE @table TABLE ( id VARCHAR(50) ) DECLARE @x INT = 0 DECLARE @firstcomma INT = 0 DECLARE @nextcomma INT = 0 SET @x = LEN(@id_list) - LEN(REPLACE(@id_list, '','', '''')) + 1 -- number of ids in id_list WHILE @x > 0 BEGIN SET @nextcomma = CASE WHEN CHARINDEX('','', @id_list, @firstcomma + 1) = 0 THEN LEN(@id_list) + 1 ELSE CHARINDEX('','', @id_list, @firstcomma + 1) END INSERT INTO @table VALUES ( SUBSTRING(@id_list, @firstcomma + 1, (@nextcomma - @firstcomma) - 1) ) SET @firstcomma = CHARINDEX('','', @id_list, @firstcomma + 1) SET @x = @x - 1 END SELECT * FROM @table


select t.OtherID,x.Kod from testData t cross apply (select Code from dbo.Split(t.Data,'','') ) x