Convertir una cadena Separada por comas en filas individuales
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 este:
| 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 de consumo lo hace realmente útil.
No quiero hacer la división en la aplicación ya que necesito paginación, así que quería explore las opciones antes de refactorizar toda la aplicación.
Es SQL Server 2008
(no-R2).
12 answers
Puede utilizar 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
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2018-04-24 20:50:14
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 cadena como XML, tabla Tally, bucle while, etc.. han sido sorprendidos por esta función STRING_SPLIT
.
Aquí hay un excelente artículo con comparación de rendimiento: Sorpresas de Rendimiento y Suposiciones: STRING_SPLIT.
Para versiones anteriores, usando tally tabla aquí hay una función de cadena dividida (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
;
Remitida desde Tally OH! Una Función Mejorada de SQL 8K "CSV Splitter"
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2017-08-01 16:26:47
Compruebe 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);
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2013-04-18 12:33:11
select t.OtherID,x.Kod
from testData t
cross apply (select Code from dbo.Split(t.Data,',') ) x
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2014-01-28 15:47:41
A partir de febrero de 2016 - vea el ejemplo de la tabla de RECUENTO - muy probablemente superará a mi TVF a continuación, a partir de febrero de 2014. Mantener el post 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 CTEs y XML. Además, un Id
explícito 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
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2017-08-01 07:07:58
Es bueno ver que se ha resuelto en la versión de 2016, pero para todos aquellos que no están en eso, aquí hay dos versiones generalizadas y simplificadas de los métodos anteriores.
El método XML es más corto, pero por supuesto requiere la cadena para permitir el truco xml (sin 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-MÉTODO 2: Unicode Amigable (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);
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2017-04-06 01:03:33
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
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2013-11-07 13:22:44
Al usar este enfoque, debe asegurarse de que ninguno de sus valores contenga algo que sea ilegal XML – user1151923
Siempre uso el método XML. Asegúrese de usar XML VÁLIDO. Tengo dos funciones para convertir entre XML válido y Texto. (Tiendo a quitar los retornos de carruaje, 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,'<','<')
SET @Text = REPLACE(@Text,'&','&')
SET @Text = REPLACE(@Text,'>','>')
SET @Text = REPLACE(@Text,'''',''')
SET @Text = REPLACE(@Text,'"','"')
RETURN @Text
END
CREATE FUNCTION dbo.udf_ConvertTextFromXML (@Text VARCHAR(MAX))
RETURNS VARCHAR(max)
AS
BEGIN
SET @Text = REPLACE(@Text,'<','<')
SET @Text = REPLACE(@Text,'&','&')
SET @Text = REPLACE(@Text,'>','>')
SET @Text = REPLACE(@Text,''','''')
SET @Text = REPLACE(@Text,'"','"')
RETURN @Text
END
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2015-03-16 16:48:07
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 simplemente una selección con 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
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2018-01-31 13:22:01
;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...
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2012-07-28 17:50:09
A continuación funciona en sql server 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á todos los productos cartesianos con las columnas de la tabla de origen más "elementos" de la tabla dividida.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2017-10-06 10:26:28
Por favor refiérase debajo de TSQL. La función STRING_SPLIT solo está disponible bajo el nivel de compatibilidad 130 y superior.
TSQL:
DECLARE @stringValue NVARCHAR (400) = 'red,blue,green,yellow,black'
DECLARE @separator CHAR=', '
SELECCIONE [valor] Como Color DESDE STRING_SPLIT (@stringValue, @ separator);
RESULTADO:
Color
Rojo azul verde amarillo negro
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/ajaxhispano.com/template/agent.layouts/content.php on line 61
2018-08-22 02:56:26