SQL Server: replace sequence of same characters inside Text Field (TSQL only)

Question

I have a text column varchar(4000) with text:

'aaabbaaacbaaaccc'

and I need to remove all duplicated chars - so only one from sequence left:

'abacbac'

It should not be a function, Procedure or CLR - Regex solution. Only true SQL select.

Currently I think about using recursive WITH clause with replace 'aa'->'a', 'bb'->'b', 'cc'->'c'.

So recursion should cycle until all duplicated sequences of that chars would be replaced.

Do you have another solution, perhaps more performant one?

PS: I searched through this site about different replace examples - they didn't suit to this case.

Answer 1

Assuming a table definition of

CREATE TABLE myTable(rowID INT IDENTITY(1,1), dupedchars NVARCHAR(4000)) 

and data..

 INSERT INTO myTable
      SELECT 'aaabbaaacbaaaccc'
       UNION
      SELECT 'abcdeeeeeffgghhaaabbbjdduuueueu999whwhwwwwwww'

this query meets your criteria

    WITH Numbers(n)
      AS
       (   SELECT 1 AS n
          UNION ALL
             SELECT (n + 1) AS n
              FROM Numbers
             WHERE n < 4000
       )
  SELECT rowid,
       (   SELECT CASE 
           WHEN SUBSTRING(dupedchars,n2.n,1) = SUBSTRING(dupedchars+' ',n2.n+1,1) THEN '' 
           ELSE SUBSTRING(dupedchars,n2.n,1) 
            END AS [text()]
           FROM myTable t2,numbers n2
          WHERE n2.n <= LEN(dupedchars)
            AND t.rowid = t2.rowid
            FOR XML path('')
       ) AS deduped
    FROM myTable  t
  OPTION(MAXRECURSION 4000)

Output

rowid   deduped
   1    abacbac
   2    abcdefghabjdueueu9whwhw