Splitting Strings and Querying JSON Arrays in SQL Server

Sooner or later you inherit a table with a column like 'red,green,blue' or '["red","green","blue"]' and a query that needs to treat those values as rows. T-SQL gives you two toolsets for this: STRING_SPLIT for delimited strings and OPENJSON for JSON arrays -- and, less obviously, OPENJSON is often the better tool for both. This guide covers splitting in each direction, the ordering and typing traps, and how to aggregate rows back into strings or arrays.

STRING_SPLIT: The Basics

STRING_SPLIT (SQL Server 2016+) is a table-valued function that returns one row per substring:

SELECT value
FROM STRING_SPLIT('red,green,blue', ',');

Against a column, use CROSS APPLY:

SELECT p.product_id, s.value AS tag
FROM products AS p
CROSS APPLY STRING_SPLIT(p.tags, ',') AS s;

It requires database compatibility level 130 or higher. On a database restored from an older instance you'll get "Invalid object name 'STRING_SPLIT'" even on a new server -- check SELECT compatibility_level FROM sys.databases WHERE name = DB_NAME(); and raise it after testing.

The Three Limits of STRING_SPLIT

1. Single-character separators only. The separator argument must be exactly one character. For multi-character delimiters like ', ' or '||', REPLACE down to a single character first:

SELECT value
FROM STRING_SPLIT(REPLACE('red || green || blue', ' || ', '|'), '|');

2. No order guarantee before 2022. The output order is not guaranteed, and there's no position column by default. SQL Server 2022 (and Azure SQL) added a third argument that enables an ordinal column:

SELECT value, ordinal
FROM STRING_SPLIT('red,green,blue', ',', 1)
ORDER BY ordinal;

If you're on 2016-2019 and need positions, STRING_SPLIT is the wrong function -- use the OPENJSON trick below, which has always returned array indexes.

3. Everything is a string. The value column is a character type. Splitting '3,1,4,1,5' and joining against an INT column works through implicit conversion, but cast explicitly to keep the join sargable and the intent obvious:

SELECT o.*
FROM orders AS o
JOIN STRING_SPLIT(@id_list, ',') AS s
  ON o.order_id = CAST(s.value AS INT);

This pattern -- passing a CSV parameter and splitting it server-side -- is the most common legitimate use of STRING_SPLIT. For large lists, a table-valued parameter is the better interface; for a handful of IDs, split-and-join is fine.

Empty Strings and Whitespace

Two consecutive separators produce an empty-string row, and spaces around separators are preserved, not trimmed:

SELECT '[' + value + ']' AS v
FROM STRING_SPLIT('a, b,,c', ',');
-- [a]  [ b]  []  [c]

Clean as you split (TRIM is 2017+; on 2016 use LTRIM(RTRIM(...))):

SELECT TRIM(value) AS v
FROM STRING_SPLIT('a, b,,c', ',')
WHERE TRIM(value) <> '';

OPENJSON as a Better Splitter

Wrap a delimited string into a JSON array and OPENJSON splits it -- with a position column, on every version since 2016:

DECLARE @csv NVARCHAR(MAX) = 'red,green,blue';
 
SELECT [key] AS position, value
FROM OPENJSON('["' + REPLACE(STRING_ESCAPE(@csv, 'json'), ',', '","') + '"]');

For a JSON array, OPENJSON's key column is documented as the element index, so ordering is guaranteed -- no 2022 requirement. The STRING_ESCAPE(@csv, 'json') call matters: without it, a quote or backslash inside the data produces invalid JSON and error 13609 ("JSON text is not properly formatted").

OPENJSON also beats STRING_SPLIT when the fragments have types. With a WITH clause you get typed columns instead of casting value everywhere -- see the JSON queries guide for the full OPENJSON ... WITH treatment.

Querying JSON Arrays Stored in Columns

If the column already holds a JSON array -- increasingly common with API payloads landed in NVARCHAR(MAX) -- skip the wrapping and shred it directly:

-- tags = '["urgent","refund","vip"]'
SELECT t.ticket_id, j.value AS tag
FROM tickets AS t
CROSS APPLY OPENJSON(t.tags) AS j;

Arrays of objects take a path and a schema:

-- items = '[{"sku":"A1","qty":2},{"sku":"B7","qty":1}]'
SELECT o.order_id, i.sku, i.qty
FROM orders AS o
CROSS APPLY OPENJSON(o.items)
WITH (sku VARCHAR(20) '$.sku', qty INT '$.qty') AS i;

"Does the array contain X?" is an EXISTS over the shredded rows:

SELECT t.ticket_id
FROM tickets AS t
WHERE EXISTS (
    SELECT 1 FROM OPENJSON(t.tags) AS j
    WHERE j.value = 'urgent'
);

This scans every row and parses every document. If one containment check is hot, materialize it as an indexed computed column (ISJSON guard plus a persisted flag, or a JSON_VALUE extraction for scalar cases) -- the same technique covered in indexes explained. SQL Server 2025's native json type adds real JSON indexes; on 2016-2022 the computed-column workaround is what exists.

Joining Back: STRING_AGG and JSON Arrays

The reverse operation -- rows to one delimited string -- is STRING_AGG (2017+):

SELECT ticket_id,
       STRING_AGG(CAST(tag AS NVARCHAR(MAX)), ', ')
         WITHIN GROUP (ORDER BY tag) AS tag_list
FROM ticket_tags
GROUP BY ticket_id;

The CAST to NVARCHAR(MAX) avoids the 8,000-byte truncation error on long lists -- the trap is covered in detail in the string functions guide.

To produce a JSON array instead of a delimited string:

-- 2016+: correlated FOR JSON, then unwrap
SELECT t.ticket_id,
       (SELECT tag FROM ticket_tags tt
        WHERE tt.ticket_id = t.ticket_id
        FOR JSON PATH) AS tags_json
FROM tickets AS t;
 
-- SQL Server 2025 / Azure SQL: the standard aggregate
SELECT ticket_id, JSON_ARRAYAGG(tag ORDER BY tag) AS tags
FROM ticket_tags
GROUP BY ticket_id;

One honesty note on JSON_ARRAYAGG: as of mid-2026 it's generally available on Azure SQL Database and Managed Instance but still in preview on SQL Server 2025 (17.x) per Microsoft's docs. For on-prem code you plan to ship, the FOR JSON PATH correlated-subquery pattern remains the safe choice. JSON_ARRAYAGG defaults to ABSENT ON NULL (NULLs silently dropped); use NULL ON NULL to keep them as JSON nulls.

Should You Store Delimited Strings at All?

Usually not. A ticket_tags(ticket_id, tag) junction table gives you foreign keys, indexes, and honest cardinality -- everything in this guide is cheaper there. The realistic cases for delimited or JSON-array columns: data you receive in that shape (API payloads, imports), genuinely schemaless attributes, and CSV parameters passed into procedures. For those, the patterns above are the toolkit; for core relational data, normalize.

Common Mistakes

When you're exploring an unfamiliar column full of delimited strings or JSON arrays, an AI-assisted editor like Mako's autocomplete helps generate the OPENJSON shredding boilerplate from a sample value instead of hand-typing paths.

Mako connects to SQL Server with AI-powered autocomplete. Try it free at mako.ai.