¿Cómo divido una cadena delimitada en SQL Server sin crear una función?

Estoy trabajando con una base de datos de SQL Server. Tengo una columna que contiene una lista delimitada, y necesito escribir una consulta que divide los valores de la lista en filas. Al navegar por StackOverflow y el rest de la web, sé que este es un problema común. De hecho, encontré un análisis extenso aquí:

http://www.sommarskog.se/arrays-in-sql.html

Desafortunadamente, cada solución que he visto en ese sitio y en otro lugar me exige crear una función. Esa no es una opción para mí: carezco de los privilegios necesarios para usar el comando CREAR.

Sin CREATE, sé que puedo usar la función PARSENAME, algo así (Gracias a Nathan Bedford en ¿Cómo puedo dividir una cadena para poder acceder al elemento x? ):

SELECT PARSENAME(REPLACE('Hello John Smith', ' ', '.'), 2) 

Sin embargo, PARSENAME funciona solo para listas de 4 elementos o menos. Mi pregunta, por lo tanto, es esta: ¿cómo escribo una consulta para dividir una cadena delimitada de más de 4 elementos sin crear nuevos objetos en la base de datos?

EDITAR:

Gracias a todos por las respuestas rápidas. Es posible que haya omitido información importante: estoy interactuando con la base de datos a través de una conexión ODBC. Además de las declaraciones CREATE, parece haber otras declaraciones que no funcionan. Por ejemplo, parece que no puedo usar DECLARE en una statement para definir una variable que se usará en otra statement. Por lo que puedo imaginar, tengo que poner todo en una sola statement SELECT (aunque WITH también parece funcionar para declarar tablas comunes). Desafortunadamente, todas las soluciones sugeridas hasta ahora parecen requerir declaraciones de variables fuera de la instrucción SELECT, y eso no está funcionando. Por favor tengan paciencia, estoy aprendiendo sobre la marcha.

Una versión que usa XML.

 declare @S varchar(100) = 'Hello John Smith' select nrvalue('.', 'varchar(50)') from (select cast(''+replace(@S, ' ', '')+'' as xml)) as s(XMLCol) cross apply s.XMLCol.nodes('r') as n(r) 

Usando una tabla en su lugar Reemplace @T con cualquier tabla que esté usando.

 -- Test table declare @T table (ID int, Col varchar(100)) insert into @T values (1, 'Hello John Smith') insert into @T values (2, 'xxx yyy zzz') select T.ID, nrvalue('.', 'varchar(50)') from @T as T cross apply (select cast(''+replace(replace(Col,'&','&'), ' ', '')+'' as xml)) as S(XMLCol) cross apply S.XMLCol.nodes('r') as n(r) 

Dividir la cadena 'Hello John Smith' sin usar una variable

 select nrvalue('.', 'varchar(50)') from (select cast(''+replace('Hello John Smith', ' ', '')+'' as xml)) as s(XMLCol) cross apply s.XMLCol.nodes('r') as n(r) 

ejemplo utilizando la tabla incorporada maestra … valores_spt

 DECLARE @String VARCHAR(1000) SELECT @String ='1,4,77,88,4546,234,2,3,54,87,9,6,4,36,6,9,9,6,4,4,68,9,0,5' SELECT SUBSTRING(',' + @String + ',', Number + 1, CHARINDEX(',', ',' + @String + ',', Number + 1) - Number -1)AS VALUE FROM master..spt_values WHERE Type = 'P' AND Number < = LEN(',' + @String + ',') - 1 AND SUBSTRING(',' + @String + ',', Number, 1) = ',' GO 

Consulte aquí para obtener más información: dividir una cadena utilizando una tabla numérica

Puede usar CTE recursivo para extraer progresivamente un elemento

Mesa de muestra

 create table aTable(a int identity primary key, b int, c varchar(100)) insert aTable values (1, 'this is a test string') insert aTable values (1, 'this is another test string') insert aTable values (2, 'here is a test string to put the others to shame') insert aTable values (4, '') insert aTable values (5, null) insert aTable values (5, '-the end- ') 

La consulta

 ;with tmp(a, b, c, position, single) as ( select a, b, STUFF(c, 1, CHARINDEX(' ', c + ' .'), ''), 1, convert(nvarchar(max),left(c, CHARINDEX(' ', c + ' .') -1)) from aTable union all select a, b, STUFF(c, 1, CHARINDEX(' ', c + ' .'), ''), position+1, convert(nvarchar(max),left(c, CHARINDEX(' ', c + ' .') -1)) from tmp where c > '') select a, b, single, position from tmp order by a, position 

Notas:

  • El delimitador aquí es un espacio único, que es lo que CHARINDEX busca. El punto en ' .' es necesario porque SQL Server normalmente no ve los espacios finales como significativos.
  • TODAS las columnas de la tabla original pueden conservarse en el CTE, solo agréguelas. Aquí muestro un ejemplo de 2 columnas a y b conservadas en el resultado, con la columna c dividida en una single y una columna adicional para indicar la posición.

Para los que lleguen tarde a esta pregunta, el artículo en http://www.sqlperformance.com/2012/07/t-sql-queries/split-strings proporciona un excelente análisis del rendimiento de varias opciones. Algunas de las opciones consideradas incluyen (copiadas del sitio para referencia):

CLR

Código .Net en http://sqlblog.com/blogs/adam_machanic/archive/2009/04/28/sqlclr-string-splitting-part-2-even-faster-even-more-scalable.aspx .

 CREATE ASSEMBLY CLRUtilities FROM 'c:\DLLs\CLRUtilities.dll' WITH PERMISSION_SET = SAFE; GO CREATE FUNCTION dbo.SplitStrings_CLR ( @List NVARCHAR(MAX), @Delimiter NVARCHAR(255) ) RETURNS TABLE ( Item NVARCHAR(4000) ) EXTERNAL NAME CLRUtilities.UserDefinedFunctions.SplitString_Multi; GO 

XML

 CREATE FUNCTION dbo.SplitStrings_XML ( @List NVARCHAR(MAX), @Delimiter NVARCHAR(255) ) RETURNS TABLE WITH SCHEMABINDING AS RETURN ( SELECT Item = yivalue('(./text())[1]', 'nvarchar(4000)') FROM ( SELECT x = CONVERT(XML, '' + REPLACE(@List, @Delimiter, '') + '').query('.') ) AS a CROSS APPLY x.nodes('i') AS y(i) ); GO 

CTE

 CREATE FUNCTION dbo.SplitStrings_CTE ( @List NVARCHAR(MAX), @Delimiter NVARCHAR(255) ) RETURNS @Items TABLE (Item NVARCHAR(4000)) WITH SCHEMABINDING AS BEGIN DECLARE @ll INT = LEN(@List) + 1, @ld INT = LEN(@Delimiter); WITH a AS ( SELECT [start] = 1, [end] = COALESCE(NULLIF(CHARINDEX(@Delimiter, @List, @ld), 0), @ll), [value] = SUBSTRING(@List, 1, COALESCE(NULLIF(CHARINDEX(@Delimiter, @List, @ld), 0), @ll) - 1) UNION ALL SELECT [start] = CONVERT(INT, [end]) + @ld, [end] = COALESCE(NULLIF(CHARINDEX(@Delimiter, @List, [end] + @ld), 0), @ll), [value] = SUBSTRING(@List, [end] + @ld, COALESCE(NULLIF(CHARINDEX(@Delimiter, @List, [end] + @ld), 0), @ll)-[end]-@ld) FROM a WHERE [end] < @ll ) INSERT @Items SELECT [value] FROM a WHERE LEN([value]) > 0 OPTION (MAXRECURSION 0); RETURN; END GO 

Función

 CREATE FUNCTION dbo.SplitStrings_Moden ( @List NVARCHAR(MAX), @Delimiter NVARCHAR(255) ) RETURNS TABLE WITH SCHEMABINDING AS RETURN 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), E2(N) AS (SELECT 1 FROM E1 a, E1 b), E4(N) AS (SELECT 1 FROM E2 a, E2 b), E42(N) AS (SELECT 1 FROM E4 a, E2 b), cteTally(N) AS (SELECT 0 UNION ALL SELECT TOP (DATALENGTH(ISNULL(@List,1))) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E42), cteStart(N1) AS (SELECT t.N+1 FROM cteTally t WHERE (SUBSTRING(@List,tN,1) = @Delimiter OR tN = 0)) SELECT Item = SUBSTRING(@List, s.N1, ISNULL(NULLIF(CHARINDEX(@Delimiter,@List,s.N1),0)-s.N1,8000)) FROM cteStart s; 

Resulta que el mejor rendimiento proviene del uso de la función CLR mientras que la solución XML también funciona bien. En casi todos los casos, el uso de una tabla de números (el enfoque no se duplicó anteriormente) conduce al peor rendimiento.

Para SQL Server> = 2016 puede usar string_split como se muestra a continuación:

 SELECT * FROM string_split('Hello John Smith', ' ') 

Salida

 +-------+ | value | +-------+ | Hello | | John | | Smith | +-------+ 

Simplemente tomaría una de las muchas funciones que crea una tabla y en lugar de devolver el valor lo coloque en una variable de tabla. Luego usa la variable de tabla. Aquí hay un ejemplo que devuelve una tabla.

http://www.codeproject.com/KB/database/SQL_UDF_to_Parse_a_String.aspx

usar un UDF tiene más sentido, es flexible para todos los proyectos, el que generalmente uso es en mi blog,

http://sqlthis.blogspot.com/2005/02/list-to-table.html

porque está escrito para tomar su cadena de entrada y un delimitador, puede ser cualquier carácter individual para un delimitador. Escribí uno en el enlace de arriba, pero luego encontré muchos sitios que publicaban una solución similar, por lo que es posible que algunas partes se hayan inspirado en los foros de los que soy miembro …

funciona, y espero que funcione también para usted.

edit 2017-08-09 según lo sugerido, he clonado el bloque de código a continuación. ¡Gracias!

 CREATE FUNCTION udfListToTable (@HList VarChar(2000), @Delimiter CHAR(1)) RETURNS @ListTable TABLE (Field1 VARCHAR(6)) AS BEGIN --By: Francisco Tapia --Date: 2/1/2005 --Purpose: To convert a Comma delimited text to a Temp Variable table To help avoid dynamic sql -- Instead you can join the temp table or use it in your where clause if a field is IN the subquery DECLARE @FieldText as VarChar(6) IF RIGHT(RTRIM(@HLIST),1) <>@Delimiter SET @HList = @HList + @Delimiter WHILE CHARINDEX(@Delimiter, @HList) > 0 BEGIN IF CHARINDEX(@Delimiter, @HList) > 0 BEGIN SELECT @FieldText =LEFT(@HList, CHARINDEX(@Delimiter, @HList)-1) END ELSE BEGIN SELECT @FieldText = RTRIM(LTRIM(@HList)) END --Insert into Variable Table INSERT INTO @ListTable(Field1) SELECT RTRIM(LTRIM(@FieldText)) --Remove Item from list SELECT @HList = RIGHT(RTRIM(@HList), LEN(RTRIM(@HList)) - CHARINDEX(@Delimiter, @HList)) END RETURN END 
  DECLARE @cols AS NVARCHAR(max), @Val VARCHAR(100)='Hi- Hello break this-Wall', @Deli VARCHAR(50)='-', @query AS NVARCHAR(max) SELECT @cols = Stuff((SELECT ',' + Quotename(id) FROM (SELECT stringpieceid AS ID, stringpiece FROM [Utility].[dbo].[Splitstringtotable](@Val, @Deli)) X FOR xml path(''), type).value('.', 'NVARCHAR(MAX)'), 1, 1, '') SELECT @query = 'SELECT * FROM (SELECT StringPieceID as ID,StringPiece from [Utility].[dbo].[SplitStringToTable](''' + @Val + ''',''' + @Deli + '''))X PIVOT ( MAX(StringPiece) for [ID] in (' + @cols + ') ) P' PRINT @query EXEC Sp_executesql @query 
 USE TRIAL GO CREATE TABLE DETAILS ( ID INT, NAME VARCHAR(50), ADDRESS VARCHAR(50) ) INSERT INTO DETAILS VALUES (100, 'POPE-JOHN-PAUL','VATICAN CIT|ROME|ITALY') ,(240, 'SIR-PAUL-McARTNEY','NEWYORK CITY|NEWYORK|USA') ,(460,'BARRACK-HUSSEIN-OBAMA','WHITE HOUSE|WASHINGTON|USA') ,(700, 'PRESIDENT-VLADAMIR-PUTIN','RED SQUARE|MOSCOW|RUSSIA') ,(950, 'NARENDRA-DAMODARDAS-MODI','10 JANPATH|NEW DELHI|INDIA') select [ID] ,[NAME] ,[ADDRESS] ,REPLACE(LEFT(NAME, CHARINDEX('-', NAME)),'-',' ') as First_Name ,CASE WHEN CHARINDEX('-',REVERSE(NAME))+ CHARINDEX('-',NAME) < LEN(NAME) THEN SUBSTRING(NAME, CHARINDEX('-', (NAME)) + 1, LEN(NAME) - CHARINDEX('-' , REVERSE(NAME)) - CHARINDEX('-', NAME)) ELSE 'NULL END AS Middle_Name ,REPLACE(REVERSE( SUBSTRING( REVERSE(NAME), 1, CHARINDEX('- ',REVERSE(NAME)))), '-','') AS Last_Name ,REPLACE(LEFT(ADDRESS, CHARINDEX('|', ADDRESS)),'|',' ') AS Locality ,CASE WHEN CHARINDEX('|',REVERSE(ADDRESS))+ CHARINDEX('|',ADDRESS) < LEN(ADDRESS) THEN SUBSTRING(ADDRESS, CHARINDEX('|', (ADDRESS))+1, LEN(ADDRESS)- CHARINDEX('|', REVERSE(ADDRESS))-CHARINDEX('|',ADDRESS)) ELSE 'Null' END AS STATE ,REPLACE(REVERSE(SUBSTRING(REVERSE(ADDRESS),1 ,CHARINDEX('|',REVERSE(ADDRESS)))),'|','') AS Country FROM DETAILS SELECT CHARINDEX('-', REVERSE(NAME)) AS LAST,CHARINDEX('-',NAME)AS FIRST, LEN(NAME) AS LENGTH FROM DETAILS 

- AVISARME SI TIENES DUDAS ENTENDIENDO EL CÓDIGO

Aquí hay una función de análisis definida por el usuario que habilita SQL Server que también funciona de forma similar a la función “Dividir” de VB. Diseñado para el aprovechamiento interactivo; por ejemplo, para analizar datos dentro de un Procedimiento almacenado llamado desde una API externa.

https://gallery.technet.microsoft.com/scriptcenter/User-def-function-enabling-98561cce