Remove some characters of a column from a table type variable

Question

I get a table type variable like this

color
-------------
Black
Blue<br/>PMS 285C
Green<br/>PMS 360
Red<br/>PMS 199

I want to make them like

color
-------------
Black, Blue, Green, Red

But I am having a very difficult time to remove the "&lt.." stuff. I followed this answer: Is there a way to loop through a table variable in TSQL without using a cursor? To create a temp table to loop and remove the characters, but still does not work

DECLARE @result int, @i int = 0, @colors varchar(800);
DECLARE @results table(color varchar(40));
INSERT INTO @results SELECT color FROM...

DECLARE @temp table(Id INT, color varchar(40));
INSERT INTO @temp SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS Id, color FROM @results

WHILE (SELECT Count(*) FROM @temp WHERE color like '%&lt%') > 0
BEGIN
    SELECT Top 1 @i = Id FROM @temp WHERE color like '%&lt%'
    UPDATE @temp Set color = (case when color like '%&lt%'
                         then left(color, charindex('&lt', color) - 1)
                         else color
                    end) Where Id = @i
    Delete @temp Where Id = @i
END

I still get

color
------------------------------------------------------------------------------
 Black, Blue<br/>PMS 285C, Green<br/>PMS 360, Red<br/>PMS 199

Could anyone help me please? Or give some suggestions.

Thanks

Answer 1

This uses left() with charindex() to get the left part of each value before the &; and uses the stuff() with select ... for xml path ('') method of string concatenation.

select stuff((
  select ', '+left(color,charindex('&',color+'&')-1)
  from @temp
  for xml path (''), type).value('.','varchar(max)')
  ,1,2,'')

test setup:

create table temp (color varchar(32))
insert into temp values 
 ('Black')
,('Blue<br/>PMS 285C')
,('Green<br/>PMS 360')
,('Red<br/>PMS 199')

select stuff((
  select ', '+left(color,charindex('&',color+'&')-1)
  from temp
  for xml path (''), type).value('.','varchar(max)')
  ,1,2,'')

returns: Black, Blue, Green, Red

rextester demo: http://rextester.com/TWESQZ38491

Reference:

• Concatenating Row Values in Transact-SQL - Anith Sen

Answer 2

Another version base on previous answer

    create table temp (color varchar(32))
    insert into temp values 
     ('Black')
    ,('Blue<br/>PMS 285C')
    ,('Green<br/>PMS 360')
    ,('Red<br/>PMS 199')



    declare @var varchar(max)
    set @var=''
    select @var=@var+case when charindex('&',color)=0 then color 
        else left(color, charindex('&',color)-1) end +', ' from temp
    select left(@var, len(@var)-1)