Post reply

Replace repeated Char in a string by the Char itself

  • 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.

  • May 19, 2011 at 2:23 pm

    #1327346

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

  • May 19, 2011 at 2:29 pm

    #1327352

    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!

  • May 19, 2011 at 2:31 pm

    #1327354

    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!

  • May 19, 2011 at 2:40 pm

    #1327364

    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!

  • May 19, 2011 at 2:42 pm

    #1327366

    Lowell (5/19/2011)

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

    Same link as mine;-)

  • May 19, 2011 at 2:56 pm

    #1327382

    Thanks, Phil and Lowell.

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

  • May 19, 2011 at 11:14 pm

    #1327473

    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)

  • May 21, 2011 at 3:50 am

    #1328085

    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 😉

  • May 21, 2011 at 5:34 am

    #1328090

    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

  • May 21, 2011 at 10:43 am

    #1328112

    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)

  • May 21, 2011 at 10:49 am

    #1328114

    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 😀

  • May 22, 2011 at 6:34 am

    #1328180

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

  • May 22, 2011 at 11:36 am

    #1328218

    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)

  • May 22, 2011 at 2:05 pm

    #1328239

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

    Sorry for my bold comment. :unsure:

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

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

Cookies on SQLServerCentral

This website stores cookies on your computer.

These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.

To find out more about the cookies we use, see our Privacy Policy