How to split a string with a comma or a specific delimiter in SQL Server 2016 and later?
We can use
STRING_SPLIT predefined function to split a string.
STRING_SPLIT introduced on SQL Server 2016.
STRING_SPLIT (string, separator)
Example: Space character as the separator
SELECT S.[value] FROM STRING_SPLIT('AAA BBB CCC DDD EEE.', ' ') AS S;
Example: Split comma-separated value string
SELECT S.[value] FROM STRING_SPLIT('AAA,BBB,CCC,,EEE,', ',') AS S WHERE RTRIM(S.[value]) <> '';
STRING_SPLITwill return an empty string if there is nothing between separators. Condition
RTRIM(value) <> ''will remove empty tokens.
CreatedJan 19, 2020
UpdatedNov 03, 2020
Different types of variables in SQL Server
What is Cross Join in SQL Server
How delete record from parent table when there is Foreign Key relation in SQL Server
What is the differences between CHAR, NCHAR, VARCHAR and NVARCHAR in SQL Server?
How to add or remove a column from a table in SQL Server?
How to insert values into Identity Column in SQL Server?
How to get a fixed-length value in SQL Server?
What is Left Join (or) Left Outer Join in SQL Server?
How to get current week dates in SQL Server?
What are the different types of Date formats in SQL Server?