Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Replacing recurring characters in a string with single character Expand / Collapse
Author
Message
Posted Monday, January 20, 2014 5:56 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, April 14, 2014 3:07 AM
Points: 373, Visits: 50
Hi all,

I have a problem where I want to write a function to remove recurring characters from a string and replace them with a single same character.

For instance I have the string '12333345566689' and the result should be '12345689'. In Oracle I could do this with "regexp_replace('12333345566689', '(.)\1+', '\1')", but in T-SQL the only solution I could think of is something like this:

DECLARE @code NVARCHAR(255)
SET @code = '12333345566689';
SET @code = REPLACE(REPLACE(REPLACE(@Code, '1', '~1'), '1~', ''), '~1', '1');

and repeat this for 2 - 9. But I'm sure there is a more elegant version for this in SQL Server 2012. Unfortunately I haven't found any solution in other posts, so maybe someone has an idea for this?

Thanks in advance.
Post #1532588
Posted Monday, January 20, 2014 7:57 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, April 17, 2014 7:06 AM
Points: 2,404, Visits: 7,311
Probably not the best way to do this, just off the top of my head: -

DECLARE @code NVARCHAR(255);
SET @code = '12333345566689';

WITH CTE(N) AS (SELECT 1 FROM (VALUES(1),(1),(1),(1),(1),(1),(1),(1),(1),(1))a(N)),
CTE2(N) AS (SELECT 1 FROM CTE x CROSS JOIN CTE y),
CTE3(N) AS (SELECT 1 FROM CTE2 x CROSS JOIN CTE2 y),
CTE4(N) AS (SELECT 1 FROM CTE3 x CROSS JOIN CTE3 y),
CTE5(N) AS (SELECT 1 FROM CTE4 x CROSS JOIN CTE4 y),
CTE6(N) AS (SELECT TOP (LEN(@code)) 1 FROM CTE5 x CROSS JOIN CTE5 y),
TALLY(N) AS (SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) FROM CTE6)
SELECT @code = REPLACE(REPLACE(REPLACE(LTRIM(RTRIM(@code)),SUBSTRING(@code,N,1)+SUBSTRING(@code,N,1),SUBSTRING(@code,N,1)+CHAR(7)),CHAR(7)+SUBSTRING(@code,N,1),''),CHAR(7),'')
FROM TALLY;

SELECT @code;

Returns "12345689"

Based on REPLACE Multiple Spaces with One and The "Numbers" or "Tally" Table: What it is and how it replaces a loop.



Not a DBA, just trying to learn

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better, quicker answers on SQL Server performance related questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/



If you litter your database queries with nolock query hints, are you aware of the side effects?
Try reading a few of these links...

(*) Missing rows with nolock
(*) Allocation order scans with nolock
(*) Consistency issues with nolock
(*) Transient Corruption Errors in SQL Server error log caused by nolock
(*) Dirty reads, read errors, reading rows twice and missing rows with nolock


LinkedIn | Blog coming soon (for sufficiently large values of "soon" )!
Post #1532655
Posted Monday, January 20, 2014 8:14 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 6:10 PM
Points: 2,763, Visits: 5,909
Here's a different method that might work well. Should we start testing?

DECLARE @code nvarchar(4000) = '12333345566689';

with seed1 (a)
as
(
select 1 union all select 1 union all select 1 union all select 1 union all select 1 union all select 1 union all select 1 union all select 1 union all
select 1 union all select 1 union all select 1 union all select 1 union all select 1 union all select 1 union all select 1 union all select 1
),
numbers (n) as
(
select top (LEN(@code)) row_number() over (order by (select null))
from seed1 s1, seed1 s2, seed1 s3
)
select a.b.value('(./text())[1]', 'nvarchar(4000)') as [text]
from (
select CASE WHEN SUBSTRING( @code, n, 1) = SUBSTRING( @code, n - 1, 1) THEN ''
ELSE SUBSTRING( @code, n, 1) END
FROM numbers
for xml path (''), type
)a(b)




Luis C.
I am a great believer in luck, and I find the harder I work the more I have of it. Stephen Leacock

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1532672
Posted Monday, January 20, 2014 7:37 PM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Thursday, April 17, 2014 4:39 AM
Points: 818, Visits: 2,485
A slight variation to Luis's method and implemented as an inline table valued function

CREATE FUNCTION compressDuplicates(@code nvarchar(4000))
RETURNS TABLE WITH SCHEMABINDING AS
RETURN
WITH cteTally as (
SELECT TOP(LEN(@code) - 1) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) N
FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) E1(N)
,(VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) E2(N)
,(VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) E3(N)
),
startpattern as (
SELECT SUBSTRING(@code,1,1) P
UNION ALL
SELECT SUBSTRING(@code,N+1,1) P
FROM cteTally
WHERE SUBSTRING(@code,N,1) <> SUBSTRING(@code,N+1,1) COLLATE Latin1_General_BIN -- Change to suit comparison required
)
SELECT CAST(r AS NVARCHAR(4000)) result
FROM (
SELECT P
FROM startpattern
FOR XML PATH('')
) AS A(R);

If there is non numerics in your string you will need to decide how to handle case, etc and set the collation accordingly

Post #1532835
Posted Monday, January 20, 2014 11:38 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 2:24 AM
Points: 3,591, Visits: 5,100
Just to be different, and seeing as how we are in the SQL 2012 forum which allows me to use LAG, I will offer this:

WITH SampleData AS
(
SELECT code=CAST('12333345566689333' AS NVARCHAR(4000))
UNION ALL SELECT '12333345566689'
),
Tally (n) AS
(
SELECT TOP (4000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM sys.all_columns
),
SplitString AS
(
SELECT code, s, p, n
FROM
(
SELECT code, s, p=LAG(s, 1, '') OVER (PARTITION BY code ORDER BY n), n
FROM
(
SELECT code, n, s=SUBSTRING(code, n, 1)
FROM SampleData a
CROSS APPLY
(
SELECT TOP (LEN(code)) n
FROM Tally
) b
WHERE SUBSTRING(code, n, 1) <> ''
) a
) a
WHERE s <> p
)
SELECT code
,ReducedString=
(
SELECT s + ''
FROM SplitString b
WHERE a.code = b.code
ORDER BY n
FOR XML PATH('')
)
FROM SampleData a;





My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1532860
Posted Tuesday, January 21, 2014 12:01 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 2:24 AM
Points: 3,591, Visits: 5,100
OK, so the purists will argue that LAG is overkill, and for them I offer this instead:

WITH SampleData AS
(
SELECT code=CAST('12333345566689333' AS NVARCHAR(4000))
UNION ALL SELECT '12333345566689'
),
Tally (n) AS
(
SELECT TOP (4000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM sys.all_columns
),
SplitString AS
(
SELECT code, s, n
FROM
(
SELECT code, n, s=SUBSTRING(code, n, 1)
FROM SampleData a
CROSS APPLY
(
SELECT TOP (LEN(code)) n
FROM Tally
) b
WHERE SUBSTRING(code, n, 1) <> SUBSTRING(code, n-1, 1)
) a
)
SELECT code
,ReducedString=
(
SELECT s + ''
FROM SplitString b
WHERE a.code = b.code
ORDER BY n
FOR XML PATH('')
)
FROM SampleData a;





My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1532870
Posted Tuesday, January 21, 2014 2:10 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Monday, April 14, 2014 3:07 AM
Points: 373, Visits: 50
Many thanks for your anwers.

I tried the two different approaches from dwain.c with my sample table with 100k nvarchar(50) strings. On my machine with Win 8 64, SQL Server 2012, Core i7 2.4 Ghz and 16 GB RAM the second version without LAG function took around 5 seconds. The first version with LAG executed for 12 minutes when I aborted it!! Slight difference...

With the other three approaches I have to admit that I can't get it to work with my sample table. How can I incorporate a column from my table into the code instead of the @code parameter?
Anyway, I think the 5 seconds will be hard to beat.
Post #1532910
Posted Tuesday, January 21, 2014 2:55 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 2:24 AM
Points: 3,591, Visits: 5,100
j.heidrich (1/21/2014)
Many thanks for your anwers.

I tried the two different approaches from dwain.c with my sample table with 100k nvarchar(50) strings. On my machine with Win 8 64, SQL Server 2012, Core i7 2.4 Ghz and 16 GB RAM the second version without LAG function took around 5 seconds. The first version with LAG executed for 12 minutes when I aborted it!! Slight difference...

With the other three approaches I have to admit that I can't get it to work with my sample table. How can I incorporate a column from my table into the code instead of the @code parameter?
Anyway, I think the 5 seconds will be hard to beat.


Thanks for posting those performance results. I was just having a bit of fun with LAG, knowing it wouldn't be as good as the option without it. I'm casting my net wide to find an instance where LAG beats an earlier code pattern in performance and your test shows me this ain't one of 'em.



My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1532926
Posted Tuesday, January 21, 2014 2:58 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 2:24 AM
Points: 3,591, Visits: 5,100
MickyT's FUNCTION should work like this with your table replacing SampleData:

WITH SampleData AS
(
SELECT code=CAST('12333345566689333' AS NVARCHAR(4000))
UNION ALL SELECT '12333345566689'
)
SELECT *
FROM SampleData
CROSS APPLY compressDuplicates(code);





My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1532927
Posted Tuesday, January 21, 2014 6:46 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, April 17, 2014 9:33 AM
Points: 6,754, Visits: 12,854
Couple of small changes to MickeyT's otherwise excellent function:

DECLARE @code nvarchar(4000);
SET @code = '99944777777775588888888888812' ;

WITH cteTally as (
SELECT TOP(LEN(@code) - 1) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) N
FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) E1(N)
,(VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) E2(N)
,(VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) E3(N)
),
startpattern as (
SELECT SUBSTRING(@code,1,1) P
UNION ALL
SELECT SUBSTRING(@code,N+1,1) P
FROM cteTally
WHERE SUBSTRING(@code,N,1) <> SUBSTRING(@code,N+1,1) COLLATE Latin1_General_BIN -- Change to suit comparison required
)
SELECT CAST(r AS NVARCHAR(4000)) result
FROM (
SELECT P
FROM startpattern
FOR XML PATH('')
) AS A(R);

-- result: "<P>9</P><P>4</P><P>7</P><P>5</P><P>8</P><P>1</P><P>2</P>"
--------------------------------------------------------------------------------------
WITH cteTally as (
SELECT TOP(LEN(@code) - 1) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) N
FROM (VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) E1(N)
,(VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) E2(N)
,(VALUES (0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) E3(N)
),
startpattern as (
SELECT FirstNumOfSet = SUBSTRING(@code,n,1)
FROM cteTally
WHERE SUBSTRING(@code,N,1) <> SUBSTRING(@code,N-1,1) COLLATE Latin1_General_BIN -- Change to suit comparison required
)

SELECT result = CAST(XMLresult AS NVARCHAR(4000))
FROM (
SELECT FirstNumOfSet AS [text()]
FROM startpattern
FOR XML PATH('')
) d (XMLresult);


-- result: "947581"



“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Post #1533027
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse