SQL Server: Extract or Parse words from a column which are not available in other specified columns

This is a typical parsing task. We needed to extract or parse words from a column that are not available in few specific columns. I have explained below with a comprehensive example.

To Create the table execute below script

Create table data
(
SlNo int identity(1,1),
MainColumn varchar(50),
SubCol1 varchar(50),
SubCol2 varchar(50),
SubCol3 varchar(50),
[Output] varchar(50),
)

insert into data (MainColumn,SubCol1,SubCol2,SubCol3)
select 'aaa, bbb, ccc','aaa','','' UNION
select 'aaa, bbb, ccc','','aaa','' UNION
select 'aaa, bbb, ccc','bbb','','' UNION
select 'aaa, bbb, ccc','','','bbb' UNION
select 'aaa, bbb, ccc','','','' UNION
select 'aaa, bbb, ccc','aaa','bbb','ccc' UNION
select 'aaa, bbb, ccc','ccc','','' UNION
select 'aaa, bbb, ccc','','ccc','bbb' UNION
select 'aaa, bbb, ccc','ccc','aaa','' UNION
select 'aaa, bbb, ccc','bbb','','aaa'

select * from data

So to get the required output(as shown in above screenshot) logic is here.

• Update the MainColumn data to Output column.
• Check SubCol1 value availability under Output column and replace the word with Blank if available.
• Check SubCol2 value availability under Output column and replace the word with Blank if available.
• Check SubCol3 value availability under Output column and replace the word with Blank if available.
• Replace or Trim unwanted comma with Blank.

I would like to explain the solution in two ways.

Dissected way:

Update data set [output] = MainColumn
Update data set [output] = Replace([output],isnull(SubCol1,''),'') -- execute only once
Update data set [output] = Replace([output],isnull(SubCol2,''),'') -- execute only once
Update data set [output] = Replace([output],isnull(SubCol3,''),'') -- execute only once

Simplified way:

select *,Replace(Replace(Replace(MainColumn,Isnull(SubCol1,''),''),Isnull(SubCol2,''),''),Isnull(SubCol3,''),'') As NewOutput from data

Once the above query updated, we need to remove unwanted comma's. This can be done with below query.

Update data SET [output]=REPLACE(LTRIM(RTRIM(Replace([output],',',' '))),' ',',')
Update data SET [output]=REPLACE([output],',,',',')