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.

Syntax:

STRING_SPLIT (string, separator)

Example: Space character as the separator

SELECT S.[value]  
FROM STRING_SPLIT('AAA BBB CCC DDD EEE.', ' ') AS S;

Result:

Example: Split comma-separated value string

SELECT S.[value]
FROM STRING_SPLIT('AAA,BBB,CCC,,EEE,', ',') AS S
WHERE RTRIM(S.[value]) <> '';

Note: STRING_SPLIT will return an empty string if there is nothing between separators. Condition RTRIM(value) <> '' will remove empty tokens.

Result: