MSSQL - Change Delimiters

[jackriches]

I'm trying to achieve the following, currently caught around the axle. I'm using MS SQL SERVER 2008 R2 and have a nvarchar(255) column with the following data:

1;#AAA;#2;#BBB;#3;#CCC

This could continue with #4, #5 etc so no bounds to this, only the field size.

My objective is to have the query results displaying

AAA, BBB, CCC, DDD, ..........

Thanks in Advance

JR :confused:

[Old Pedant]

SELECT REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(field,'#',''),';',','),'0',''),'1',''),'2','').... AS altered

maybe??

It's ugly, but it should work.

[jackriches]

Thanks, this is what I created

select REPLACE(replace
(replace(replace
(replace(replace
(replace(replace
(replace(replace(field,'1;#',''),';#2;#',', '),
';#3;#',', '),';#4;#',', '),';#5;#',', '),';#6;#',', ')
,';#7;#',', '),';#8;#',', '),';#9;#',', '),';#10;#',', ') AS fieldclean

from table
where field Is not Null

//JR