Usando T-SQL, devuelve el enésimo elemento delimitado de una cadena
Necesito crear una función que devolverá el enésimo elemento de una cadena delimitada.
Para un proyecto de migración de datos, estoy convirtiendo registros de auditoría JSON almacenados en una base de datos de SQL Server en un informe estructurado mediante un script SQL. El objetivo es entregar un script SQL y una función SQL utilizada por el script sin ningún código.
(Esta es una solución a corto plazo que se utilizará mientras se agrega una nueva función de auditoría a la aplicación ASP.NET/MVC)
No faltan ejemplos de cadenas delimitadas para tablas disponibles. Elegí un ejemplo de expresión de tabla común http://www.sqlperformance.com/2012/07/t-sql-queries/split-strings
Ejemplo: quiero devolver 67 de '1,222,2,67,888,1111'
Esta es la respuesta más fácil para recuperar el 67 (¡ tipo seguro! ):
SELECT CAST('<x>' + REPLACE('1,222,2,67,888,1111',',','</x><x>') + '</x>' AS XML).value('/x[4]','int')
A continuación encontrará ejemplos de cómo utilizar esto con variables para la cadena, el delimitador y la posición (incluso para casos extremos con caracteres XML prohibidos)
##El fácil
Esta pregunta no se trata de un enfoque de división de cadenas , sino de cómo obtener el enésimo elemento . La forma más sencilla y totalmente lineal sería esta en mi opinión:
Esta es una verdadera frase para delimitar la parte 2 por un espacio:
DECLARE @input NVARCHAR(100)=N'part1 part2 part3';
SELECT CAST(N'<x>' + REPLACE(@input,N' ',N'</x><x>') + N'</x>' AS XML).value('/x[2]','nvarchar(max)')
##Las variables se pueden utilizar con sql:variable()osql:column()
Por supuesto, puede usar variables para delimitador y posición (úselas sql:columnpara recuperar la posición directamente del valor de una consulta):
DECLARE @dlmt NVARCHAR(10)=N' ';
DECLARE @pos INT = 2;
SELECT CAST(N'<x>' + REPLACE(@input,@dlmt,N'</x><x>') + N'</x>' AS XML).value('/x[sql:variable("@pos")][1]','nvarchar(max)')
##Edge-Case con caracteres XML prohibidos
Si su cadena puede incluir caracteres prohibidos , aún puede hacerlo de esta manera. Simplemente utilícelo FOR XML PATHprimero en su cadena para reemplazar implícitamente todos los caracteres prohibidos con la secuencia de escape adecuada.
Es un caso muy especial si, además, su delimitador es el punto y coma . En este caso, reemplazo el delimitador primero por '#DLMT#' y, finalmente, lo reemplazo por las etiquetas XML:
SET @input=N'Some <, > and &;Other äöü@€;One more';
SET @dlmt=N';';
SELECT CAST(N'<x>' + REPLACE((SELECT REPLACE(@input,@dlmt,'#DLMT#') AS [*] FOR XML PATH('')),N'#DLMT#',N'</x><x>') + N'</x>' AS XML).value('/x[sql:variable("@pos")][1]','nvarchar(max)');
##ACTUALIZACIÓN para SQL Server 2016+
Lamentablemente, los desarrolladores olvidaron devolver el índice de la pieza con STRING_SPLIT. Pero, al usar SQL-Server 2016+, existe JSON_VALUEy OPENJSON.
Con JSON_VALUEpodemos pasar la posición como matriz del índice.
Porque OPENJSONla documentación dice claramente:
Cuando OPENJSON analiza una matriz JSON, la función devuelve los índices de los elementos en el texto JSON como claves.
Una cadena como 1,2,3no necesita más que corchetes: [1,2,3].
Una cadena de palabras como this is an exampledebe ser ["this","is","an"," example"].
Estas son operaciones de cuerdas muy sencillas. Pruébalo:
DECLARE @str VARCHAR(100)='Hello John Smith';
DECLARE @position INT = 2;
--We can build the json-path '$[1]' using CONCAT
SELECT JSON_VALUE('["' + REPLACE(@str,' ','","') + '"]',CONCAT('$[',@position-1,']'));
--Consulte esto para obtener una posición segura para dividir cadenas ( de base cero ):
SELECT JsonArray.[key] AS [Position]
,JsonArray.[value] AS [Part]
FROM OPENJSON('["' + REPLACE(@str,' ','","') + '"]') JsonArray
En esta publicación probé varios enfoques y descubrí que OPENJSONes realmente rápido. Incluso mucho más rápido que el famoso método "delimitedSplit8k()"...
##ACTUALIZACIÓN 2: Obtenga los valores con seguridad de tipos
Podemos usar una matriz dentro de una matriz simplemente usando doubled [[]]. Esto permite una WITHcláusula escrita:
DECLARE @SomeDelimitedString VARCHAR(100)='part1|1|20190920';
DECLARE @JsonArray NVARCHAR(MAX)=CONCAT('[["',REPLACE(@SomeDelimitedString,'|','","'),'"]]');
SELECT @SomeDelimitedString AS TheOriginal
,@JsonArray AS TransformedToJSON
,ValuesFromTheArray.*
FROM OPENJSON(@JsonArray)
WITH(TheFirstFragment VARCHAR(100) '$[0]'
,TheSecondFragment INT '$[1]'
,TheThirdFragment DATE '$[2]') ValuesFromTheArray
En Azure SQL Database y en SQL Server 2022, STRING_SPLITahora tiene un parámetro ordinal opcional. Si el parámetro se omite o 0se pasa, entonces la función actúa como lo hacía antes y simplemente devuelve una valuecolumna y el orden no está garantizado. Si pasa el parámetro con el valor 1, la función devuelve 2 columnas, valuey ordinalque (como era de esperar) proporciona la posición ordinal del valor dentro de la cadena.
Entonces, si quisieras el cuarto valor delimitado de la cadena, '1,222,2,67,888,1111'podrías hacer lo siguiente:
SELECT [value]
FROM STRING_SPLIT('1,222,2,67,888,1111',',',1)
WHERE ordinal = 4;
Si el valor estuviera en una columna, se vería así:
SELECT SS.[value]
FROM dbo.YourTable YT
CROSS APPLY STRING_SPLIT(YT.YourColumn,',',1) SS
WHERE SS.ordinal = 4;
