What STRING_SPLIT Does
STRING_SPLIT takes a text value and a delimiter and returns a table of rows, one per item.
SELECT value
FROM STRING_SPLIT('A,B,C', ',');
Result
This simple output becomes powerful when combined with IN, JOIN, or APPLY.
Filtering a Table Using STRING_SPLIT
A common scenario: you receive a parameter like 'New,Pending,Closed' and need all rows where Stage matches any of those values.
DECLARE @Stages varchar(100) = 'New,Pending,Closed';
SELECT *
FROM Tickets
WHERE Stage IN (
SELECT TRIM(value)
FROM STRING_SPLIT(@Stages, ',')
);
Why TRIM?
Because users love to type "New, Pending, Closed" — and trimming avoids mismatches.
Joining STRING_SPLIT to a Table
Instead of IN, you can join directly:
DECLARE @Stages varchar(100) = 'New,Pending,Closed';
SELECT t.*
FROM Tickets t
JOIN STRING_SPLIT(@Stages, ',') s
ON t.Stage = TRIM(s.value);
This is especially useful when you need additional filtering or grouping.
STRING_SPLIT With Ordinal Support (SQL Server 2022+)
Newer versions can return the ordinal position of each item:
SELECT value, ordinal
FROM STRING_SPLIT('A,B,C', ',', 1);
Result
| value |
ordinal |
| A |
1 |
| B |
2 |
| C |
3 |
This enables ordered reconstruction, ranking, and sequence-based logic.
Using STRING_SPLIT in Stored Procedures
If you're passing a comma-separated list from .NET:
CREATE PROCEDURE GetTicketsByStage
@Stages varchar(200)
AS
BEGIN
SELECT *
FROM Tickets
WHERE Stage IN (
SELECT TRIM(value)
FROM STRING_SPLIT(@Stages, ',')
);
END
And from VB.NET:
cmd.Parameters.AddWithValue("@Stages", "New,Pending,Closed")
Performance Notes
STRING_SPLIT is set-based and much faster than XML or WHILE loops.- Works best when the list is short (typical UI filters).
- For large lists (thousands of items), consider table-valued parameters instead.
When Not to Use STRING_SPLIT
Avoid it when:
- You need guaranteed ordering on older SQL Server versions.
- You’re splitting extremely large strings.
- You can redesign the schema to avoid multi-value fields entirely.
Conclusion
STRING_SPLIT is one of those features that quietly removes a decade of workarounds. It’s simple, fast, and perfect for filtering against comma-separated parameters coming from your application. Whether you're cleaning up legacy code or building new APIs, it’s a tool worth using.