CODEWITHSUNDEEP
sql-servert-sql
Mario Trento
Mario Trento

Reputation: 523

TSQL, check if comman has space after it for string

I have a string I need to feed into function /it's all about aka alphabethizing names in format Dow, John W with exact casing. In my case I need to space after comma for my function, and some strings don't have them, how to do it in most effective way? I tried to do something like below with range [] but it doesn't work. Basically I want logic: if not space after comma, then insert space, I don't want to touch function itself, as it's legacy stuff.

DECLARE @inpstring VARCHAR(100) = 'DOW,JohN   W'
SELECT  Replace(@inpstring,'[A-Z],[A-Z]','[A-Z], [A-Z]')
-- s'be 'Dow, John   W'

Tx M

Upvotes: 1

Views: 256

Answers (1)

Lukasz Szozda
Lukasz Szozda

Reputation: 176189

Assuming there is only one , in string you can use:

DECLARE @inpstring VARCHAR(100) = 'DOW,JohN   W'
SELECT CASE 
        WHEN PATINDEX('%,[A-Z]%', @inpstring) > 0 THEN Replace(@inpstring,',',', ')
        ELSE @inpstring
       END

LiveDemo

And for multiple commas:

DECLARE @inpstring VARCHAR(100) = 'A,B,C, D'
SELECT @inpstring = 
    CASE 
      WHEN PATINDEX('%,[A-Z]%', @inpstring) > 0 
      THEN REPLACE(Replace(@inpstring,',',', '), ',  ', ', ')
      ELSE @inpstring
    END

PRINT(@inpstring);

LiveDemo2

Upvotes: 1

Related Questions