Función dividida equivalente en T-SQL?

Estoy buscando dividir ‘1,2,3,4,5,6,7,8,9,10,11,12,13,14,15 …’ (delimitado por comas) en una tabla o tabla variable .

¿Alguien tiene una función que devuelve cada uno en una fila?

Aquí hay una solución algo pasada de moda:

/* Splits string into parts delimitered with specified character. */ CREATE FUNCTION [dbo].[SDF_SplitString] ( @sString nvarchar(2048), @cDelimiter nchar(1) ) RETURNS @tParts TABLE ( part nvarchar(2048) ) AS BEGIN if @sString is null return declare @iStart int, @iPos int if substring( @sString, 1, 1 ) = @cDelimiter begin set @iStart = 2 insert into @tParts values( null ) end else set @iStart = 1 while 1=1 begin set @iPos = charindex( @cDelimiter, @sString, @iStart ) if @iPos = 0 set @iPos = len( @sString )+1 if @iPos - @iStart > 0 insert into @tParts values ( substring( @sString, @iStart, @iPos-@iStart )) else insert into @tParts values( null ) set @iStart = @iPos+1 if @iStart > len( @sString ) break end RETURN END 

En SQL Server 2008 puede lograr lo mismo con el código .NET. Tal vez funcionaría más rápido, pero definitivamente este enfoque es más fácil de administrar.

Prueba esto

 DECLARE @xml xml, @str varchar(100), @delimiter varchar(10) SET @str = '1,2,3,4,5,6,7,8,9,10,11,12,13,14,15' SET @delimiter = ',' SET @xml = cast((''+replace(@str, @delimiter, '')+'') as xml) SELECT C.value('.', 'varchar(10)') as value FROM @xml.nodes('X') as X(C) 

O

 DECLARE @str varchar(100), @delimiter varchar(10) SET @str = '1,2,3,4,5,6,7,8,9,10,11,12,13,14,15' SET @delimiter = ',' ;WITH cte AS ( SELECT 0 a, 1 b UNION ALL SELECT b, CHARINDEX(@delimiter, @str, b) + LEN(@delimiter) FROM CTE WHERE b > a ) SELECT SUBSTRING(@str, a, CASE WHEN b > LEN(@delimiter) THEN b - a - LEN(@delimiter) ELSE LEN(@str) - a + 1 END) value FROM cte WHERE a > 0 

Hay muchas más formas de hacer lo mismo aquí ¿Cómo dividir la cadena delimitada por comas?

Ha etiquetado este SQL Server 2008, pero es probable que los futuros visitantes de esta pregunta (que utilizan SQL Server 2016+) quieran saber acerca de STRING_SPLIT .

Con esta nueva función incorporada ahora puedes usar

 SELECT TRY_CAST(value AS INT) FROM STRING_SPLIT ('1,2,3,4,5,6,7,8,9,10,11,12,13,14,15', ',') 

Algunas restricciones de esta función y algunos resultados prometedores de las pruebas de rendimiento se encuentran en esta publicación de blog de Aaron Bertrand .

Esto es más parecido a .NET, para aquellos de ustedes que están familiarizados con esa función:

 CREATE FUNCTION dbo.[String.Split] ( @Text VARCHAR(MAX), @Delimiter VARCHAR(100), @Index INT ) RETURNS VARCHAR(MAX) AS BEGIN DECLARE @A TABLE (ID INT IDENTITY, V VARCHAR(MAX)); DECLARE @R VARCHAR(MAX); WITH CTE AS ( SELECT 0 A, 1 B UNION ALL SELECT B, CONVERT(INT,CHARINDEX(@Delimiter, @Text, B) + LEN(@Delimiter)) FROM CTE WHERE B > A ) INSERT @A(V) SELECT SUBSTRING(@Text,A,CASE WHEN B > LEN(@Delimiter) THEN BA-LEN(@Delimiter) ELSE LEN(@Text) - A + 1 END) VALUE FROM CTE WHERE A >0 SELECT @R = V FROM @A WHERE ID = @Index + 1 RETURN @R END SELECT dbo.[String.Split]('121,2,3,0',',',1) -- gives '2' 

aquí está la función de división que pediste

 CREATE FUNCTION [dbo].[split]( @delimited NVARCHAR(MAX), @delimiter NVARCHAR(100) ) RETURNS @t TABLE (id INT IDENTITY(1,1), val NVARCHAR(MAX)) AS BEGIN DECLARE @xml XML SET @xml = N'' + REPLACE(@delimited,@delimiter,'') + '' INSERT INTO @t(val) SELECT r.value('.','varchar(MAX)') as item FROM @xml.nodes('/t') as records(r) RETURN END 

ejecutar la función de esta manera

 select * from dbo.split('1,2,3,4,5,6,7,8,9,10,11,12,13,14,15',',') 
 DECLARE @InputString NVARCHAR(MAX) = 'token1,token2,token3,token4,token5' , @delimiter varchar(10) = ',' DECLARE @xml AS XML = CAST((''+REPLACE(@InputString,@delimiter ,'')+'') AS XML) SELECT C.value('.', 'varchar(10)') AS value FROM @xml.nodes('X') as X(C) 

Fuente de esta respuesta: http://sqlhint.com/sqlserver/how-to/best-split-function-tsql-delimited

Estoy tentado de exprimir mi solución favorita. La tabla resultante constará de 2 columnas: PosIdx para la posición del entero encontrado; y Valor en entero.

 create function FnSplitToTableInt ( @param nvarchar(4000) ) returns table as return with Numbers(Number) as ( select 1 union all select Number + 1 from Numbers where Number < 4000 ), Found as ( select Number as PosIdx, convert(int, ltrim(rtrim(convert(nvarchar(4000), substring(@param, Number, charindex(N',' collate Latin1_General_BIN, @param + N',', Number) - Number))))) as Value from Numbers where Number <= len(@param) and substring(N',' + @param, Number, 1) = N',' collate Latin1_General_BIN ) select PosIdx, case when isnumeric(Value) = 1 then convert(int, Value) else convert(int, null) end as Value from Found 

Funciona mediante el uso de CTE recursivo como la lista de posiciones, de 1 a 100 de manera predeterminada. Si necesita trabajar con cadenas de más de 100, simplemente llame a esta función usando 'option (maxrecursion 4000)' como la siguiente:

 select * from FnSplitToTableInt ( '9, 8, 7, 6, 5, 4, 3, 2, 1, 0, ' + '9, 8, 7, 6, 5, 4, 3, 2, 1, 0, ' + '9, 8, 7, 6, 5, 4, 3, 2, 1, 0, ' + '9, 8, 7, 6, 5, 4, 3, 2, 1, 0, ' + '9, 8, 7, 6, 5, 4, 3, 2, 1, 0' ) option (maxrecursion 4000) 

Esta es otra versión que realmente no tiene ninguna restricción (por ejemplo: caracteres especiales al usar el enfoque xml, cantidad de registros en el enfoque CTE) y se ejecuta mucho más rápido en base a una prueba en registros de 10M + con una longitud promedio de fuente de 4000. Espero que podría ayudar.

 Create function [dbo].[udf_split] ( @ListString nvarchar(max), @Delimiter nvarchar(1000), @IncludeEmpty bit) Returns @ListTable TABLE (ID int, ListValue nvarchar(1000)) AS BEGIN Declare @CurrentPosition int, @NextPosition int, @Item nvarchar(max), @ID int, @L int Select @ID = 1, @L = len(replace(@Delimiter,' ','^')), @ListString = @ListString + @Delimiter, @CurrentPosition = 1 Select @NextPosition = Charindex(@Delimiter, @ListString, @CurrentPosition) While @NextPosition > 0 Begin Set @Item = LTRIM(RTRIM(SUBSTRING(@ListString, @CurrentPosition, @NextPosition-@CurrentPosition))) If @IncludeEmpty=1 or LEN(@Item)>0 Begin Insert Into @ListTable (ID, ListValue) Values (@ID, @Item) Set @ID = @ID+1 End Set @CurrentPosition = @NextPosition+@L Set @NextPosition = Charindex(@Delimiter, @ListString, @CurrentPosition) End RETURN END 
 CREATE FUNCTION Split ( @delimited nvarchar(max), @delimiter nvarchar(100) ) RETURNS @t TABLE ( -- Id column can be commented out, not required for sql splitting string id int identity(1,1), -- I use this column for numbering splitted parts val nvarchar(max) ) AS BEGIN declare @xml xml set @xml = N'' + replace(@delimited,@delimiter,'') + '' insert into @t(val) select r.value('.','varchar(max)') as item from @xml.nodes('//root/r') as records(r) RETURN END GO 

uso

 Select * from dbo.Split(N'1,2,3,4,6',',') 
 /* *Object: UserDefinedFunction [dbo].[Split] Script Date: 10/04/2013 18:18:38* */ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER FUNCTION [dbo].[Split] (@List varchar(8000),@SplitOn Nvarchar(5)) RETURNS @RtnValue table (Id int identity(1,1),Value nvarchar(100)) AS BEGIN Set @List = Replace(@List,'''','') While (Charindex(@SplitOn,@List)>0) Begin Insert Into @RtnValue (value) Select Value = ltrim(rtrim(Substring(@List,1,Charindex(@SplitOn,@List)-1))) Set @List = Substring(@List,Charindex(@SplitOn,@List)+len(@SplitOn),len(@List)) End Insert Into @RtnValue (Value) Select Value = ltrim(rtrim(@List)) Return END go Select * From [Clv].[Split] ('1,2,3,3,3,3,',',') GO 

Este blog vino con una solución bastante buena usando XML en T-SQL.

Esta es la función que surgió en función de ese blog (cambiar el nombre de la función y el tipo de resultado emitidos por necesidad):

 SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE FUNCTION [dbo].[SplitIntoBigints] (@List varchar(MAX), @Splitter char) RETURNS TABLE AS RETURN ( WITH SplittedXML AS( SELECT CAST('' + REPLACE(@List, @Splitter, '') + '' AS XML) AS Splitted ) SELECT xvvalue('.', 'bigint') AS Value FROM SplittedXML CROSS APPLY Splitted.nodes('//v') x(v) ) GO 
 CREATE Function [dbo].[CsvToInt] ( @Array varchar(4000)) returns @IntTable table (IntValue int) AS begin declare @separator char(1) set @separator = ',' declare @separator_position int declare @array_value varchar(4000) set @array = @array + ',' while patindex('%,%' , @array) <> 0 begin select @separator_position = patindex('%,%' , @array) select @array_value = left(@array, @separator_position - 1) Insert @IntTable Values (Cast(@array_value as int)) select @array = stuff(@array, 1, @separator_position, '') end 

El uso de la tabla de conteo aquí es una función de secuencia dividida (mejor enfoque posible) por Jeff Moden

 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,tN,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”

Este simple CTE dará lo que se necesita:

 DECLARE @csv varchar(max) = '1,2,3,4,5,6,7,8,9,10,11,12,13,14,15'; --append comma to the list for CTE to work correctly SET @csv = @csv + ','; --remove double commas (empty entries) SET @csv = replace(@csv, ',,', ','); WITH CteCsv AS ( SELECT CHARINDEX(',', @csv) idx, SUBSTRING(@csv, 1, CHARINDEX(',', @csv) - 1) [Value] UNION ALL SELECT CHARINDEX(',', @csv, idx + 1), SUBSTRING(@csv, idx + 1, CHARINDEX(',', @csv, idx + 1) - idx - 1) FROM CteCsv WHERE CHARINDEX(',', @csv, idx + 1) > 0 ) SELECT [Value] FROM CteCsv 

Usted escribe esta función en el servidor sql después de que se resuelva ese problema.

http://csharpdotnetsol.blogspot.in/2013/12/csv-function-in-sql-server-for-divide.html