STRING_SPLIT (Transact-SQL)

  • Artículo

Se aplica a: SQL Server 2016 (13.x) y versiones posteriores Azure SQL DatabaseAzure SQL Managed InstanceAzure Synapse AnalyticsPunto de conexión de análisis SQL en Microsoft FabricAlmacenamiento de Microsoft Fabric

STRING_SPLIT es una función con valores de tabla que divide una cadena en filas de subcadenas, según un carácter separador especificado.

Nivel de compatibilidad 130

STRING_SPLIT requiere que el nivel de compatibilidad sea al menos 130. Cuando el nivel de compatibilidad es inferior a 130, el motor de base de datos no puede encontrar la función STRING_SPLIT.

Para cambiar el nivel de compatibilidad de una base de datos, vea Ver o cambiar el nivel de compatibilidad de una base de datos.

Nota:

No es necesario configurar la compatibilidad para STRING_SPLIT en Azure Synapse Analytics.

Convenciones de sintaxis de Transact-SQL

Sintaxis

STRING_SPLIT ( string , separator [ , enable_ordinal ] )

Argumentos

string

Una expresión de cualquier tipo de carácter (por ejemplo nvarchar, varchar, nchar o char).

separator

Una expresión de carácter único de cualquier tipo de caracteres (por ejemplo, nvarchar(1), varchar(1), nchar(1) o char(1)) que se usa como separador para subcadenas concatenadas.

enable_ordinal

Se aplica a: Azure SQL Database, Azure SQL Managed Instance, Azure Synapse Analytics (solo grupo de SQL sin servidor), SQL Server 2022 (16.x) y versiones posteriores

Expresiónint o bit que actúa como marca para habilitar o deshabilitar la columna de salida ordinal. Un valor de 1 habilita la columna ordinal. Si se omite enable_ordinal, NULL, o tiene un valor de 0, la columna ordinal se deshabilita.

Tipos de valores devueltos

Si la columna de salida ordinal no está habilitada, STRING_SPLIT devuelve una tabla de una sola columna cuyas filas son las subcadenas. El nombre de la columna es value. Devuelve nvarchar si cualquiera de los argumentos de entrada es nvarchar o nchar. De lo contrario, devuelve varchar. La longitud del tipo de valor devuelto es igual a la longitud del argumento de cadena.

Si al argumento enable_ordinal se le pasa un valor de 1, se devuelve una segunda columna llamada ordinal que consta de los valores de índice basados en 1 de la posición de cada subcadena en la cadena de entrada. El tipo devuelto es bigint.

Comentarios

STRING_SPLIT introduce una cadena que tiene subcadenas delimitadas y un carácter que se usará como el delimitador o el separador. Opcionalmente, la función admite un tercer argumento con un valor de 0 o 1 que deshabilita o habilita, respectivamente, la columna de salida ordinal.

STRING_SPLIT genera una tabla de una o dos columnas, dependiendo del argumento enable_ordinal.

  • Si enable_ordinal es NULL, se omite o tiene el valor 0, STRING_SPLIT devuelve una tabla de una sola columna cuyas filas contienen las subcadenas. El nombre de la columna de salida es value.

  • Si enable_ordinal tiene un valor de 1, la función devuelve una tabla de dos columnas, incluyendo la columna ordinal, que consta de los valores de índice basados en 1 de las subcadenas de la cadena de entrada original.

El argumento enable_ordinal debe ser un valor constante, no una columna o variable. También debe ser un tipo de datos bit o int con un valor de 0 o 1. De lo contrario, la función producirá un error.

Las filas de salida pueden estar en cualquier orden. No se garantiza que el orden coincida con el de las subcadenas de la cadena de entrada. Puede invalidar el orden final usando una cláusula ORDER BY en la instrucción SELECT; por ejemplo, ORDER BY value o ORDER BY ordinal.

0x0000 (char(0)) es un carácter no definido en las intercalaciones de Windows y no se puede incluir en STRING_SPLIT.

Las subcadenas vacías de longitud cero están presentes cuando la cadena de entrada contiene dos o más repeticiones consecutivas del carácter delimitador. Las subcadenas vacías se tratan de la misma forma que las subcadenas sin formato. Puede filtrar las filas que contienen la subcadena vacía usando la cláusula WHERE; por ejemplo, WHERE value <> ''. Si la cadena de entrada es NULL, la función con valores de tabla STRING_SPLIT devuelve una tabla vacía.

Por ejemplo, la siguiente instrucción SELECT utiliza el carácter de espacio como el separador:

SELECT value FROM STRING_SPLIT('Lorem ipsum dolor sit amet.', ' ');

En una ejecución práctica, la instrucción SELECT anterior devolvió la siguiente tabla de resultados:

value
Lorem
ipsum
dolor
sit
amet.

En el ejemplo siguiente se habilita la columna ordinal pasando 1 para el tercer argumento opcional:

SELECT * FROM STRING_SPLIT('Lorem ipsum dolor sit amet.', ' ', 1);

A continuación, esta instrucción devuelve la siguiente tabla de resultados:

value ordinal
Lorem 1
ipsum 2
dolor 3
sit 4
amet. 5

Ejemplos

A. Dividir una cadena de valores separados por coma

Se analiza una lista de valores separados por coma y se devuelven todos los tokens que no están vacíos:

DECLARE @tags NVARCHAR(400) = 'clothing,road,,touring,bike'

SELECT value
FROM STRING_SPLIT(@tags, ',')
WHERE RTRIM(value) <> '';

STRING_SPLIT devuelve una cadena vacía si no hay nada entre el separador. La condición RTRIM(value) <> '' quita tokens vacíos.

B. Dividir una cadena de valores separados por coma en una columna

La tabla Product tiene una columna con una lista de etiquetas separadas por comas que se muestran en el siguiente ejemplo:

ProductId Nombre Etiquetas
1 Full-Finger Gloves clothing,road,touring,bike
2 LL Headset bike
3 HL Mountain Frame bike,mountain

Con la siguiente consulta se transforma cada lista de etiquetas y las combina con la fila original:

SELECT ProductId, Name, value
FROM Product
    CROSS APPLY STRING_SPLIT(Tags, ',');

El conjunto de resultados es el siguiente:

ProductId NOMBRE Value
1 Full-Finger Gloves clothing
1 Full-Finger Gloves road
1 Full-Finger Gloves touring
1 Full-Finger Gloves bike
2 LL Headset bike
3 HL Mountain Frame bike
3 HL Mountain Frame mountain

Nota:

El orden de la salida puede variar, ya que no se garantiza que el orden coincida con el de las subcadenas de la cadena de entrada.

C. Agregación por valores

Los usuarios deben crear un informe en el que se muestre el número de productos por etiqueta, ordenadas por número de productos, y filtrar solo las etiquetas con más de dos productos.

SELECT value as tag, COUNT(*) AS [number_of_articles]
FROM Product
    CROSS APPLY STRING_SPLIT(Tags, ',')
GROUP BY value
HAVING COUNT(*) > 2
ORDER BY COUNT(*) DESC;

D. Buscar por el valor de tabla

Los desarrolladores deben crear consultas que hallen artículos a partir de palabras clave. Pueden usar las siguientes consultas:

Para encontrar productos con una sola etiqueta (clothing):

SELECT ProductId, Name, Tags
FROM Product
WHERE 'clothing' IN (SELECT value FROM STRING_SPLIT(Tags, ','));

Para encontrar productos con dos etiquetas especificadas (clothing y road):

SELECT ProductId, Name, Tags
FROM Product
WHERE EXISTS (SELECT *
    FROM STRING_SPLIT(Tags, ',')
    WHERE value IN ('clothing', 'road'));

E. Encontrar filas por la lista de valores

Los desarrolladores deben crear una consulta que busque los artículos a partir de una lista de identificadores. Pueden usar la siguiente consulta:

SELECT ProductId, Name, Tags
FROM Product
JOIN STRING_SPLIT('1,2,3',',')
    ON value = ProductId;

El uso de STRING_SPLIT anterior es un reemplazo para un antipatrón común. Este tipo de antipatrón puede implicar la creación de una cadena SQL dinámica en el nivel de aplicación o en Transact-SQL. O bien un antipatrón puede lograrse mediante el operador LIKE. Vea la instrucción SELECT de ejemplo siguiente:

SELECT ProductId, Name, Tags
FROM Product
WHERE ',1,2,3,' LIKE '%,' + CAST(ProductId AS VARCHAR(20)) + ',%';

F. Buscar filas por valores ordinales

La instrucción siguiente busca todas las filas con un valor de índice par:

SELECT *
FROM STRING_SPLIT('Austin,Texas,Seattle,Washington,Denver,Colorado', ',', 1)
WHERE ordinal % 2 = 0;

La instrucción anterior devuelve la tabla siguiente:

value ordinal
Texas 2
Washington 4
Colorado 6

G. Ordenar filas por valores ordinales

La siguiente sentencia devuelve los valores de la subcadena dividida de la cadena de entrada y sus valores ordinales, ordenados por la columna ordinal:

SELECT * FROM STRING_SPLIT('E-D-C-B-A', '-', 1) ORDER BY ordinal DESC;

La instrucción anterior devuelve la tabla siguiente:

value ordinal
A 5
B 4
C 3
D 2
E 1

Contenido relacionado