how to replace uncounted multiple commas into one comma

Question

how to replace uncounted multiple commas into one comma

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

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

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

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 you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

Steve Collins SSCrazy Points: 2072 More actions · Answer 2

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

Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

Phil Parkin SSC Guru Points: 246711 More actions · Answer 3

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 you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

Steve Collins SSCrazy Points: 2072 More actions · Answer 4

That was the correct clarification 🙂

Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können