STRING_SPLIT

The STRING_SPLIT function in SQL Server is a powerful and efficient way to split a string into multiple rows based on a specified delimiter. Introduced in SQL Server 2016, this function has become an essential tool for developers and database administrators who frequently work with comma-separated values, dynamic lists, or delimited input parameters.

In this article, you’ll learn what the STRING_SPLIT function is, how it works, its syntax, real-world examples, limitations, performance considerations, and best practices.

What Is STRING_SPLIT in SQL Server?

STRING_SPLIT is a string function that splits a single string into multiple rows using a specified delimiter. Each substring is returned as a separate row in a result set.

This function is commonly used when:

Handling comma-separated values (CSV).
Parsing user input.
Filtering records based on dynamic lists.
Normalizing denormalized string data.

STRING_SPLIT Syntax

STRING_SPLIT ( string , separator )

Parameters

string – The input string to be split.
separator – A single-character delimiter used to split the string.

Return Value

Returns a single-column table.
Column name: value.
Data type: nvarchar or varchar (based on input).

Basic Example of STRING_SPLIT

Let’s start with a simple example.

SELECT value
FROM STRING_SPLIT('Apple,Banana,Orange', ',');

Output:
Apple
Banana
Orange

Each element separated by a comma is returned as a separate row.

Using STRING_SPLIT with a Table

A common real-world scenario is filtering rows using a delimited list.

Example Table

CREATE TABLE Products (
    ProductID INT,
    ProductName VARCHAR(50)
);

Insert Sample Data

INSERT INTO Products VALUES
(1, 'Laptop'),
(2, 'Tablet'),
(3, 'Phone'),
(4, 'Monitor');

Filter Using STRING_SPLIT

SELECT *
FROM Products
WHERE ProductName IN (
    SELECT value
    FROM STRING_SPLIT('Laptop,Phone', ',')
);

This query dynamically filters products without needing hardcoded IN clauses.

STRING_SPLIT with JOIN

You can also use STRING_SPLIT with joins for better performance and readability.

SELECT p.*
FROM Products p
JOIN STRING_SPLIT('Laptop,Phone', ',') s
    ON p.ProductName = s.value;

This approach is often preferred for complex queries.

Handling Whitespace in STRING_SPLIT

One common issue is extra spaces in delimited strings.

Problem Example

SELECT value
FROM STRING_SPLIT('Laptop, Phone, Tablet', ',');

Result:

Laptop
Phone
Tablet

Notice the leading spaces.

Solution: Use TRIM

SELECT TRIM(value) AS CleanValue
FROM STRING_SPLIT('Laptop, Phone, Tablet', ',');

This ensures clean and consistent output.

STRING_SPLIT and Ordering Results

Important Limitation

By default, STRING_SPLIT does not guarantee the order of returned values.

SELECT value
FROM STRING_SPLIT('A,B,C,D', ',');

The output order may vary depending on execution plans.

STRING_SPLIT with Ordinal (SQL Server 2022+)

Starting with SQL Server 2022, STRING_SPLIT supports an optional ordinal column.

Syntax with Ordinal

STRING_SPLIT ( string, separator, enable_ordinal )

Example

SELECT value, ordinal
FROM STRING_SPLIT('A,B,C,D', ',', 1);

Output:

value	ordinal
A	1
B	2
C	3
D	4

This feature makes STRING_SPLIT suitable for scenarios where order matters.

STRING_SPLIT vs Traditional String-Splitting Methods

Before SQL Server 2016, developers relied on:

XML parsing.
Recursive CTEs.
WHILE loops.
Custom split functions.

Advantages of STRING_SPLIT

Built-in and optimized.
Cleaner syntax.
Better performance.
Reduced maintenance.

Performance Considerations

While STRING_SPLIT is efficient, keep these tips in mind:

Avoid splitting very large strings in high-frequency queries.
Use proper indexing when joining split results with tables
Prefer JOIN over IN for large datasets.
Use STRING_SPLIT once and reuse results via CTE or temp tables.

Common Use Cases for STRING_SPLIT

Parsing CSV input parameters in stored procedures.

Filtering reports dynamically.
Converting multi-value columns into normalized rows.
Processing user-selected filters.
Migrating legacy denormalized data.

Limitations of STRING_SPLIT

Despite its usefulness, STRING_SPLIT has some limitations:

Separator must be a single character.
No built-in trimming (use TRIM).
Ordering not guaranteed (before SQL Server 2022).
Returns only one column (value).

Best Practices for Using STRING_SPLIT

Always clean results using TRIM.
Use SQL Server 2022 ordinal support when order matters.
Avoid storing comma-separated values in tables when possible.
Combine with proper indexing for performance.
Use CTEs for complex logic.

Conclusion

The SQL Server STRING_SPLIT function is a simple yet powerful tool for handling delimited strings in modern SQL Server environments. Whether you’re filtering records, parsing user input, or cleaning legacy data, STRING_SPLIT offers a clean, efficient, and maintainable solution.

With enhancements like ordinal support in SQL Server 2022, STRING_SPLIT has become even more versatile. By understanding its syntax, limitations, and best practices, you can write cleaner SQL code and improve query performance.

If you work with SQL Server regularly, mastering STRING_SPLIT is a must-have skill.