Replace multiple instance of a character with a single instance in sql

Question

Probably it's a duplicate, but I couldn't find a solution.

Requirement:

I have the below strings:

Heelloo
Heeelloo
Heeeelloo
Heeeeeelloo
Heeeeeeeelloo
.
.
.
Heeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeelloo

Expected output: Hello

What is the best way to achieve this in SQL?

Version I am using :

Microsoft SQL Server 2012 - 10.0.7365.0 (X64) Jul 28 2015 00:39:54 Copyright (c) 
Microsoft Corporation Parallel Data Warehouse (64-bit) on Windows NT 6.2 <X64> 
(Build 9200: )

Possible duplicate of Removing repeated duplicated characters — Bridge
– Bridge, Commented Jan 14, 2016 at 14:53
Nice challenge! You would need an approach that recognises ee and oo as errors but not ll. Because language is so complex I'm not sure SQL Server is the best tool for the job. Have you considered writing a small app to loop over and spell check the field? — David Rushton
– David Rushton, Commented Jan 14, 2016 at 14:57
Please avoid editing your question to ask more or different questions. It gets too chaotic that way. — TT.
– TT., Commented Jan 14, 2016 at 16:23

Gordon Linoff · Accepted Answer · 2017-02-24 13:27:56Z

12

There is a nice trick for removing such duplicates for a single letter:

select replace(replace(replace(col, 'e', '<>'
                              ), '><', ''
                      ), '<>', 'e'
              )

This does require two characters ("<" and ">") that are not in the string (or more specifically, not in the string next to each other). The particular characters are not important.

How does this work?

Heeello
H<><><>llo
H<>llo
Hello

edited Feb 24, 2017 at 13:27

answered Jan 14, 2016 at 14:58

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

SouravA Over a year ago

Wow!! Awesome answer!

SouravA Over a year ago

Gordon - I am really sorry but I had an additional requirement here and I forgot to ask it in the question. Can I edit the current question please? (I would have to unmark your reply as an answer).

Gordon Linoff Over a year ago

@SouravA . . . Your original question was quite clear. You should actually ask the revised question as a new question. Changing the question invalidates answers, which draw downvotes. Hence, it is rude.

SouravA Over a year ago

Gordon - point taken. I will edit it back to original and mark this as an answer.

SouravA Over a year ago

stackoverflow.com/q/34805863/2993606 Here is the link to the new question.

|

Lukasz Szozda · Accepted Answer · 2016-01-14 15:51:02Z

Based on T-SQL String Manipulation Tips and Techniques, Part 1 especially part Replacing Multiple Contiguous Spaces With a Single Space and idea of Peter Larsson, a SQL Server MVP:

Then, the solution involves three steps (assuming the token is ~):

Replace in @str each occurrence of ' ' (space) with '~ ' (token plus space).

Replace in the result of the previous step each occurrence of ' ~' (space plus token) with '' (an empty string).

Replace in the result of the previous step each occurrence of '~ ' (token plus space) with ' ' (space).

CREATE TABLE #tab(val NVARCHAR(100));

INSERT INTO #tab
SELECT 'Hello'
UNION ALL SELECT 'Heello'
UNION ALL SELECT 'Heeello'
UNION ALL SELECT 'Heeeello'
UNION ALL SELECT 'Heeeeeello'
UNION ALL SELECT 'Heeeeeeeello'
UNION ALL SELECT 'Heeeeeeeeeello';

-- version for one vowel(it can be enhanced to handle other if needed)
SELECT val,
cleaned = REPLACE(
           REPLACE(
            REPLACE(
            REPLACE(REPLACE(val, REPLICATE('e', 8), '^^')
              , 'e', '~ ')
            , ' ~', '')
          , '~ ', 'e')
          ,'^^','ee')              
FROM #tab;

LiveDemo

Output:

╔════════════════╦═════════╗
║      val       ║ cleaned ║
╠════════════════╬═════════╣
║ Hello          ║ Hello   ║
║ Heello         ║ Hello   ║
║ Heeello        ║ Hello   ║
║ Heeeello       ║ Hello   ║
║ Heeeeeello     ║ Hello   ║
║ Heeeeeeeello   ║ Heello  ║
║ Heeeeeeeeeello ║ Heeello ║
╚════════════════╩═════════╝

I upvoted it. If you copy this answer on that question, I will mark it as an answer. Gordon got there first so marked his as an answer.

Atheer Mostafa · Accepted Answer · 2016-01-15 09:41:42Z

2

Try this user defined function:

CREATE FUNCTION TrimDuplicates(@String varchar(max))
RETURNS varchar(max)
AS
BEGIN
    while CHARINDEX('ee',@String)>0 BEGIN SET @String=REPLACE(@String,'ee','e') END
    while CHARINDEX('oo',@String)>0 BEGIN SET @String=REPLACE(@String,'oo','o') END
    RETURN @String
END

Example Usage:

select dbo.TrimDuplicates ('Heeeeeeeelloo')

returns Hello

edited Jan 15, 2016 at 9:41

answered Jan 14, 2016 at 15:17

Atheer Mostafa

7353 silver badges8 bronze badges

Collectives™ on Stack Overflow

Replace multiple instance of a character with a single instance in sql

3 Answers 3

6 Comments

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

6 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related