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: 246960 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;

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

Steve Collins SSCrazy Points: 2157 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: 246960 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?

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

Steve Collins SSCrazy Points: 2157 More actions · Answer 4

That was the correct clarification 🙂

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