Need UDF that removes consecutive repeating characters from string

Question

Need UDF that removes consecutive repeating characters from string

o2srule

Grasshopper

Points: 24
More actions
June 26, 2008 at 3:25 pm

#77028

Hello,
Does anyone have a UDF that takes a string and returns a string which removes repeating consecutive characters?
For example, an input string of:
'testting a strinnng liike this 12324456778889'
returns:
'testing a string like this 1232456789'
TIA

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

o2srule Grasshopper Points: 24 More actions · Answer 1

Here's what I came up with but it seems kind of kludgy, although it works. It's limited to strings that are 255 in length. I'd like it to determine the string length dynamically. Can anyone suggest a better way?

CREATE FUNCTION dbo.fn_RemoveRepeatedChars

(@string VARCHAR(255))

RETURNS VARCHAR(255)

AS

BEGIN

DECLARE @str VARCHAR(255)

DECLARE @count INT

DECLARE @result VARCHAR(255)

SET @count=1

SET @str = @string

DECLARE @prev_char varchar(1)

DECLARE @curr_char varchar(1)

WHILE (@count <=255)

BEGIN

IF (@result IS NULL)

BEGIN

SET @result=''

END

SET @curr_char = substring(@str,@count,@count)

IF @count > 1

BEGIN

SET @prev_char = substring(@str,@count-1,@count-1)

IF @prev_char <> @curr_char

SET @result = @result + @curr_char

END

ELSE

SET @result = @result + @curr_char

SET @count=@count+1

END

RETURN @result

END

GO

Jeff Moden SSC Guru Points: 1004692 More actions · Answer 2

o2srule (6/26/2008)
Here's what I came up with but it seems kind of kludgy, although it works. It's limited to strings that are 255 in length. I'd like it to determine the string length dynamically. Can anyone suggest a better way?

Sure... lemme show you how... let's use a Tally table to replace the explicit loop... if you don't already have a Tally table, check out the following article...

http://www.sqlservercentral.com/articles/TSQL/62867/

... then, you can write a nice, short, fast function like this...

CREATE FUNCTION dbo.fnRemoveRepeatedChars

(@SomeString VARCHAR(8000))

RETURNS VARCHAR(8000)

AS

BEGIN

--===== Declare the cleaned return string

DECLARE @CleanString VARCHAR(8000)

--===== Clean the string using Tally table to replace loop

SELECT @CleanString = COALESCE(@CleanString,'') + SUBSTRING(@SomeString,t.N,1)

FROM dbo.Tally t

WHERE t.N <= LEN(@SomeString)

AND SUBSTRING(@SomeString,t.N,1) <> SUBSTRING(@SomeString,t.N-1,1)

RETURN @CleanString

END

... and here's an example call to the function...

--===== Demo the function

SELECT dbo.fnRemoveRepeatedChars('12324456778889')

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)