I need SQL to eliminate duplicates in a string.

Ex : In a table a column named subject holds string like : 'maths,science,chemistry,maths,biology' ,

I need the output like this  'maths,science,chemistry,biology'. (elimated maths which occurred twice ).

Thanks in advance for your responses.

declare @t table (sid int, col varchar(50))
 Insert into @t values(1,'maths,science,chemistry,maths,biology'),(2,'maths,science,chemistry,maths,biology')

;with mycte as (SELECT distinct sid, S.a.value('.', 'VARCHAR(100)') AS splitVal
FROM
(
SELECT *,CAST (N'<H><r>' + REPLACE(col, ',', '</r><r>')+ '</r></H>' AS XML) AS [vals]
FROM @t) d
  
CROSS APPLY d.[vals].nodes('/H/r') S(a)
)
Select distinct m1.sid, newcol=stuff(( select  ','+splitVal   FROM mycte m
 WHERE m.sID = m1.sID FOR XML PATH('')),1,1,'') 
from mycte m1

• Proposed as answer by Saurabh Bhadauria 4 hours 34 minutes ago

Hi,

I am not sure if has performance issue, check the code below.

declare @word nvarchar(100) = 'maths,science,chemistry,maths,biology'
declare @find nvarchar(20) = 'maths'
select substring(@word, 1, CHARINDEX(@find, @word, CHARINDEX(@find, @word)+len(@find))-1)
  +replace(substring(@word, CHARINDEX(@find, @word, CHARINDEX(@find, @word)+len(@find)-1), len(@word)),@find,'')

DECLARE  @Input Varchar(4000) = 'maths,science,chemistry,maths,biology',
		 @Xml XML

SELECT 	@Xml = Cast('<Data>'+ Replace(@Input,',','</Data><Data>')+ '</Data>' As Xml) 


SELECT STUFF((SELECT DISTINCT ',' + Op FROM (
SELECT t.value('.', 'Varchar(50)') As Op
  FROM @Xml.nodes('/Data') As x(t))Data
  FOR XML PATH ('')),1,1,'') As FinalData

this?

SELECT STUFF(SELECT DISTINCT ',' + Val
FROM dbo.ParseValues('maths,science,chemistry,maths,biology',',')f
FOR XML PATH('')),1,1,'')

ParseValues can be found here

http://visakhm.blogspot.in/2010/02/parsing-delimited-string.html