Replace repeated Char in a string by the Char itself

Question

Replace repeated Char in a string by the Char itself

Henk Schreij

SSCarpal Tunnel

Points: 4554
More actions
May 19, 2011 at 2:18 pm

#239974

I am looking for an easy way to replace the repeated char # in a string by just one #.
E.g. ####A##B#####C### must be tranformed in: #A#B#C#
There must be an easy way to do this (no loops, no nested REPLACE), but I can't remember.:crying:
Please help me if you know. I've seen it somewhere in SqlServerCentral.com but I can't find it anymore.

Viewing 15 posts - 1 through 15 (of 16 total)

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

Phil Parkin SSC Guru Points: 246960 More actions · Answer 1

Have a look here[/url] - I'm sure you can adapt it to your needs.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Lowell SSC Guru Points: 323518 More actions · Answer 2

there is an easy way, but it does require nested replaces; it will remove any number of repeating chars with a single one.

I THINK this is how it works by replacing odd # chars/even # charsand odd/even a second time.

With MySampleData

AS

(

SELECT '####A##B#####C#####' As TheString UNION ALL

SELECT '####A##B#######C###' As TheString UNION ALL

SELECT '###################A##B#####C###' As TheString UNION ALL

SELECT '######A##B#######C#####' As TheString

)

SELECT REPLACE(REPLACE(REPLACE(REPLACE(TheString, '###','#'),'##','#'),'###','#'),'##','#')

FROM MySampleData

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Lowell SSC Guru Points: 323518 More actions · Answer 3

ok what i posted works for my sample data, but not all situations...but i swear it's a variation of that concept.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Lowell SSC Guru Points: 323518 More actions · Answer 4

here it is, i think: Jeff Modens "Replace Multiple Spaces With One" Article[/url]

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Phil Parkin SSC Guru Points: 246960 More actions · Answer 5

Lowell (5/19/2011)
here it is, i think: []bJeff Modens "Replace Multiple Spaces With One" Article[/url][/b]

Same link as mine;-)

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Henk Schreij SSCarpal Tunnel Points: 4554 More actions · Answer 6

Thanks, Phil and Lowell.

This will (after some modification for # in stead of a space) do the job:smooooth:

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 7

Henk Schreij (5/19/2011)
I am looking for an easy way to replace the repeated char # in a string by just one #.
E.g. ####A##B#####C### must be tranformed in: #A#B#C#
There must be an easy way to do this (no loops, no nested REPLACE), but I can't remember.:crying:
Please help me if you know. I've seen it somewhere in SqlServerCentral.com but I can't find it anymore.

That's too bad. The use of a nested REPLACE is on of the very fastest methods.

--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)

Henk Schreij SSCarpal Tunnel Points: 4554 More actions · Answer 8

Sorry Jef, the REPLACE method which you explained in your article, enhanced by Michael Meierruth, was just what I was looking for.

Till that moment my mind was struggled in the thought of:

"replace 3 #'s by 2 #'s, replace 4 #'s by 2 #'s, replace 5 #'s by 2 #'s, replace 6 #'s by 2 #'s, etc."

Thanks for your article and the following discussion 😉

Henk Schreij SSCarpal Tunnel Points: 4554 More actions · Answer 9

The result:

WITH TestData

AS

(

SELECT '####A##B#####C#####' AS TestString UNION ALL

SELECT '####A##B#######C###' AS TestString UNION ALL

SELECT '#A##B############C#' AS TestString UNION ALL

SELECT '#A##B##########C###' AS TestString UNION ALL

SELECT '#A##B####################C###' AS TestString

)

SELECT REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(TestString,

'#################################','#'),/*33 0..151534*/

'#################','#'), /*17 0..4622*/

'#########','#'), /*9 0..286*/

'#####','#'), /*5 0..38*/

'###','#'), /*3 0..10*/

'##','#'),

'##','#')

FROM TestData

And when used in a VARCHAR(40) field:

SELECT REPLACE(REPLACE(REPLACE(REPLACE(TestString,

'#####','#'), /*5 0..38*/

'###','#'), /*3 0..10*/

'##','#'),

'##','#')

FROM TestData

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 10

Henk Schreij (5/21/2011)
Sorry Jef, the REPLACE method which you explained in your article, enhanced by Michael Meierruth, was just what I was looking for.
Till that moment my mind was struggled in the thought of:
"replace 3 #'s by 2 #'s, replace 4 #'s by 2 #'s, replace 5 #'s by 2 #'s, replace 6 #'s by 2 #'s, etc."
Thanks for your article and the following discussion 😉

Thanks for the feedback, Henk. And well done on your solution.

Now all you have to do is replace all occurances of "Jef" with "Jeff" and you'll be golden. 😉

--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)

Phil Parkin SSC Guru Points: 246960 More actions · Answer 11

Now all you have to do is replace all occurances of "Jef" with "Jeff" and you'll be golden. [Wink]

..and all occurrences of occurance with occurrence 😀

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Henk Schreij SSCarpal Tunnel Points: 4554 More actions · Answer 12

LOL :exclamationmark:

Btw, I have a suggestion for the link you (Jeff and Phil) both mentioned:

[font="Courier New"]http://www.sqlservercentral.com/articles/Best+Practices/61537/[/font]

Forum Etiquette: How to post data/code on a forum

Lowell showed us an easier way, then "the use of a temp table", to generate testdata. The use of the WITH operator:

WITH MySampleData

AS

(

SELECT '####A##B#####C#####' AS TestRow UNION ALL

SELECT '####A##B#######C###' AS TestRow UNION ALL

SELECT '#A##B##########C###' AS TestRow UNION ALL

SELECT '######A##B#############C#####' AS TestRow

)

When you make an update of this article in the future, you could consider to mention this approach (when using SqlServer 2005 +).

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 13

The reason why I use a Temp Table for such things is because a Temp Table persists in SSMS until I drop the table or end the session. There are many times where I want to compare different methods either by Execution Plan or by performance (using SQL Profiler) and having a table that I don't have to constantly rebuild makes both processes a whole lot easier than using temporary structures such as Table Variables or CTE's. It's also more typical for such code to operate against a table rather than a variable or a CTE. Further, my "normal" tests are usually built against a million rows of test data which is typically generated in a random manner. In order to test performance, all methods being tested have to be tested against an identical set of a million rows.

--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)

Henk Schreij SSCarpal Tunnel Points: 4554 More actions · Answer 14

Jeff, you're right, thank you for your explanation.

Sorry for my bold comment. :unsure: