how to replace uncounted multiple commas into one comma

  • Phil Parkin

    SSC Guru

    Points: 244662

    There's now a well-known solution to this problem which requires no non-standard functions:

    DROP TABLE IF EXISTS #Test;

    CREATE TABLE #Test
    (
    SomeString VARCHAR(500)
    );

    INSERT #Test
    (
    SomeString
    )
    VALUES
    (',,,,,,,,,,,,,xfgdfbg,,,,,,,,,,,,,,,sdrfgsdfgdsf,asdffd')
    ,('zdvklnasv kasdvn ,,,,,,,,sdcvlk adslfknh lknj ,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,,');

    SELECT t.SomeString
    ,FixedString = REPLACE(REPLACE(REPLACE(t.SomeString, ',', '<>'), '><', ''), '<>', ',')
    FROM #Test t;

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • Steve Collins

    SSC Eights!

    Points: 889

    Imo that does not appear to be an infallible method.  Would you agree?

  • Phil Parkin

    SSC Guru

    Points: 244662

    Infallible, unless the original text contains some combinations of angled brackets, in which case alternative 'dummy characters' may be employed, for example:

    SELECT t.SomeString
    ,FixedString = REPLACE(REPLACE(REPLACE(t.SomeString, ',', '~^'), '^~', ''), '~^', ',')
    FROM #Test t;

    Unless you care to prove otherwise, with examples?

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • Steve Collins

    SSC Eights!

    Points: 889

    That was the correct clarification 🙂

Viewing 4 posts - 31 through 34 (of 34 total)

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