The SQL Booklet

Search SQL Server

Enter two or more characters.

Español

STRING_AGG and STRING_SPLIT: Gather and separate delimited text

Some outputs need to present multiple values in a single string; others receive a string with delimited elements that we must convert to rows. STRING_AGG joins and STRING_SPLIT separates.

They are useful tools at system boundaries, but a list within a column is no substitute for a properly modeled relationship.

Gather values by group

STRING_AGG combines values from several rows and places the separator between them. Because it is an aggregate, it produces one result for every group defined by the query.

STRING_AGG(Expresion, Separador)

CONVERT(NVARCHAR(MAX), p.Nombre) ensures that the aggregate works with Unicode text—an encoding capable of representing characters from many writing systems—without the short limit of an intermediate column. The conversion does not change each name; it controls the type of result built by STRING_AGG.

SELECT
    p.IdCategoria,
    STRING_AGG(
        CONVERT(NVARCHAR(MAX), p.Nombre),
        N', '
    ) WITHIN GROUP (ORDER BY p.Nombre) AS Productos
FROM dbo.Producto AS p
WHERE p.Activo = 1
GROUP BY p.IdCategoria;

STRING_AGG concatenates the names in each category and places the separator between values. WITHIN GROUP sets the order inside the string; an outer ORDER BY, when present, sorts the final rows.

NULL values—absent or unknown values—are omitted together with their separators. If we want to represent their absence, we first apply COALESCE or ISNULL.

Separate a list

STRING_SPLIT accepts a string and a separator, and returns one row for every recognized fragment.

STRING_SPLIT(Cadena, Separador)

Each separate fragment may retain spaces or contain text that does not represent a number. TRIM removes the trailing spaces and TRY_CONVERT(INT, valor) attempts to convert the result to an integer; if it cannot do so, it returns NULL instead of stopping the query with an error.

DECLARE @Ids NVARCHAR(100) = N'1, 3, 5';

SELECT
    TRY_CONVERT(INT, TRIM(s.value)) AS IdProducto
FROM STRING_SPLIT(@Ids, N',') AS s
WHERE TRY_CONVERT(INT, TRIM(s.value)) IS NOT NULL;

Each fragment is returned as a row named value. The filter discards the fragments whose conversion produced NULL.

The output order of STRING_SPLIT is not guaranteed. If the original order matters and we use SQL Server 2022 or later, we can request an ordinal column, a number that indicates each fragment’s position:

SELECT
    s.ordinal,
    TRIM(s.value) AS Valor
FROM STRING_SPLIT(N'rojo,verde,azul', N',', 1) AS s
ORDER BY s.ordinal;

The third argument must be a constant and is not available in previous versions.

Compatibility

STRING_SPLIT requires database compatibility level 130 or higher. This level determines which engine behaviors and features a database can use. STRING_AGG is available from SQL Server 2017; ordering with WITHIN GROUP depends on the supported level.

When a script must work on older installations, we check version and compatibility level instead of assuming the feature exists.

Result length

The type of the aggregate expression influences the type and length of the result. Explicitly converting to NVARCHAR(MAX) avoids unexpected limits when we concatenate many values, although we must also control the size that the client can actually consume.

When not to use a delimited list

To filter hundreds or thousands of identifiers sent by an application, a table-valued parameter—a parameter that carries several rows with a defined structure—is often clearer and more efficient. To store relationships, we use a related table instead of a comma-separated string.

We reserve these functions for presentation, bounded exchange, or data that arrives legitimately as text.

What should be checked

We review the delimiter, spaces, empty elements, conversion, duplicates, collation, and size. Collation defines how text is compared and sorted. If values can contain the delimiter itself, we need a structured format such as JSON rather than a naive split.