How to pivot given string by replacing special characters in SQL?

In an interview, I have been asked to pivot a string by replacing special characters.

Given string was 'AAA%BBB$CCC#DDD'

Expected output as shown in then image:

How can we do this in SQL Server? Thanks in advance!

Answer

In SQL Server 2014?(I guess the operating system you are using is Windows Server 2008 R2, hahaha), if you use SQL Server 2014-, basically, you need to unify the different separators first,

declare @str as varchar(1024) = 'AAA%BBB$CCC#DDD'
set @str = REPLACE(@str, '%', ',')
set @str = REPLACE(@str, '$', ',')
set @str = REPLACE(@str, '#', ',')

then like this:

;with c0 as
(
select REPLACE(@str, '"', '') + ',' as col0
), c1 as
(
select LEFT(col0, CHARINDEX(',', col0) - 1) as col1,
       STUFF(col0, 1, CHARINDEX(',', col0), '') as col2
from c0
union all
select LEFT(col2, CHARINDEX(',', col2) - 1) as col1,
       STUFF(col2, 1, CHARINDEX(',', col2), '') as col2
from c1
where LEN(col2) > 0
)

select col1 from c1 where col1 <> 'null'

OR

;with c0 as
(
select @str as s
), c1 as
(
select number as n from master.dbo.spt_values where type = 'P'
)
select SUBSTRING(c0.s, c1.n, CHARINDEX(',', c0.s+',', c1.n)-c1.n) as item,
       c1.n - LEN(REPLACE(LEFT(c0.s, c1.n), ',', '')) + 1 as pos1,
       ROW_NUMBER() over(order by c1.n) as pos2,
       c1.n, c0.s
from c0
inner join c1 on c1.n <= LEN(c0.s) and SUBSTRING(','+c0.s, c1.n, 1) = ','

OR

set @str = 'select col1 = ''' + REPLACE(@str,',',''' union all select ''') + ''''
set @str = 'select col1 from (' + @str + ') as D'
exec(@str)

OR

set @str = '(values (''' + REPLACE(@str,',',''' ),( ''') + ''')) as tb(item)'
set @str = 'select item from ' + @str
exec(@str)