SQL SERVER – Split Comma Separated Value String in a Column Using STRING_SPLIT

To Split Comma Separated Value String inputs, I use STRING_SPLIT with CROSS APPLY. The example associates each class with its student.

DECLARE @StudentClasses TABLE (ID int, Student varchar(100), Classes varchar(100));
INSERT @StudentClasses VALUES
 (1, 'Mark', 'Maths,Science,English'),
 (2, 'John', 'Science,English'),
 (3, 'Robert', 'Maths,English');
SELECT s.ID, s.Student, c.value AS Class
FROM @StudentClasses AS s
CROSS APPLY STRING_SPLIT(s.Classes, ',') AS c
ORDER BY s.ID, c.value;

The example produces seven class rows. STRING_SPLIT requires SQL Server 2016 or later with compatibility level 130 or higher. An older engine or lower level can report Invalid object name STRING_SPLIT.

The basic function doesn’t preserve token order. This query sorts classes alphabetically within each student. SQL Server 2022 added optional ordinal output. Use STRING_SPLIT(Classes, ‘,’, 1) and ORDER BY ordinal when source order matters.

Enabling ordinal alone doesn’t sort the result. Comma-delimited columns also have limitations, including empty tokens and embedded commas. A related table with one class per row is often a better storage design. Splitting remains useful for existing inputs.

Reference: STRING_SPLIT versions and ordering.

Related reading

• SQL SERVER – Fix Error: Invalid object name STRING_SPLIT

Splitting a delimited column is not a full CSV parser, it is token extraction with version and ordering rules.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.