Splitting string using CTE

DECLARE @Delimitedtext varchar(max);
DECLARE @Delimiter char(1);
SET @Delimitedtext = ‘aaa,bbbb,cccc,dddd,’;
SET @Delimiter = ‘,’;

WITH Strings(s, r)
AS
(
SELECT
SUBSTRING(@Delimitedtext,1, CHARINDEX(@Delimiter, @Delimitedtext)-1) s,
SUBSTRING(@Delimitedtext,CHARINDEX(@Delimiter, @Delimitedtext)+1, len(@Delimitedtext)) r
UNION ALL
SELECT
SUBSTRING(r,1, CHARINDEX(@Delimiter, r)-1) s,
SUBSTRING(r,CHARINDEX(@Delimiter, r)+1, len(r)) r
FROM Strings
WHERE
CHARINDEX(@Delimiter, r) > 0
)
SELECT s FROM Strings

output

s
————-
aaa
bbbb
cccc
dddd