Sql remove duplicates from comma separated string

Question

I want to make a query in sql-server which can make the following output as like column _B from column_A.Columns are varchar type.

  Column_A                                 column_B
  karim,karim,rahim,masud,raju,raju        karim,rahim,masud,raju
  jon,man,jon,kamal,kamal                  jon,man,kamal
  c,abc,abc,pot                            c,abc,pot

Answer 1

First of all: You were told in comments alread, that this is a very bad design (violating 1.NF)! If you have the slightest chance to change this, you really should... Never store more than one value within one cell!

If you have to stick with this (or in order to repair this mess), you can go like this:

This is the simplest approach I can think of: Transform the CSV to an XML and call XQuery-function distinct-values()

DECLARE @tbl TABLE(ColumnA VARCHAR(MAX));
INSERT INTO @tbl VALUES
 ('karim,karim,rahim,masud,raju,raju')
,('jon,man,jon,kamal,kamal')
,('c,abc,abc,pot');

WITH Splitted AS
(
    SELECT ColumnA 
          ,CAST('<x>' + REPLACE(ColumnA,',','</x><x>') + '</x>' AS XML) AS TheParts
    FROM @tbl 
)
SELECT ColumnA
      ,TheParts.query('distinct-values(/x/text())').value('.','varchar(250)') AS ColumnB
FROM Splitted;

The result

ColumnA                             ColumnB
karim,karim,rahim,masud,raju,raju   karim rahim masud raju
jon,man,jon,kamal,kamal             jon man kamal
c,abc,abc,pot                       c abc pot

UPDATE Keep the commas

WITH Splitted AS
(
    SELECT ColumnA 
          ,CAST('<x>' + REPLACE(ColumnA,',','</x><x>') + '</x>' AS XML) AS TheParts
    FROM @tbl 
)
SELECT ColumnA
      ,STUFF(
          (TheParts.query
          ('
          for $x in distinct-values(/x/text())
            return <x>{concat(",", $x)}</x>
          ').value('.','varchar(250)')),1,1,'') AS ColumnB
FROM Splitted;

The result

ColumnB
karim,rahim,masud,raju
jon,man,kamal
c,abc,pot