How do I remove a trailing comma when an alias is involved?

Question

CASE  WHEN Col1 = 1 THEN 

      (CASE WHEN Col2=2 THEN 'a' + ', ' END) +
      (CASE WHEN Col3=2 THEN 'b' + ', ' END) +
      (CASE WHEN Col4=2 THEN 'c' END)         
END as Names

Names can be a,b,c or a,b, or b,

How do I remove the character "," at the end? I cannot use replace or stuff etc functions as "Names" is an alias

original Query:

CASE WHEN ht.bTax=0 then

ISNULL(CASE WHEN (r1.iOccType=2) THEN p1.SFIRSTNAME + ' ' + p1.ULASTNAME END,'') + ISNULL(CASE WHEN (r2.iOcctType=2) THEN p2.SFIRSTNAME + ' ' + p2.ULASTNAME + ', ' END, '') + ISNULL(CASE WHEN (r3.iOccType=2) THEN p3.SFIRSTNAME + ' ' + p3.ULASTNAME + ', ' END, '') + ISNULL(CASE WHEN (r4.iOccType=2) THEN p4.SFIRSTNAME + ' ' + p4.ULASTNAME + ', ' END, '') + ISNULL(CASE WHEN (r5.iOccType=2) THEN p5.SFIRSTNAME + ' ' + p5.ULASTNAME + ', ' END, '') End AS Names

Try the FOR XML PATH technique. https://stackoverflow.com/a/545672 — Robert Harvey, Feb 01 '19 at 18:30
Out of curiosity, why does the use of an alias preclude using STUFF or REPLACE? Are you getting a specific error message? — Robert Harvey, Feb 01 '19 at 18:32
Did you looked into this https://stackoverflow.com/questions/16839806/replace-substring-specifically-at-the-end-of-the-character-string-in-sql — Soumen Mukherjee, Feb 01 '19 at 18:56
Maybe I was not clear. I need the answer to be a,b,c or a,b. Th point is, I am not able to remove the , from the string. — SandyN, Feb 01 '19 at 19:16

score 0 · Answer 1 · answered Feb 01 '19 at 19:06

My solution would be to add the comma to the end of the 'c' result exactly the same as the others then remove the last two characters from the string after the fact. I think it would be cleaner to put your first statement into a cte then select from it while cleaning up the string then:

with cte as( Select CASE WHEN 1 = 1 THEN (CASE WHEN Col2=2 THEN 'a, ' ELSE '' END) + (CASE WHEN Col3=2 THEN 'b, ' ELSE '' END) + (CASE WHEN Col3=2 THEN 'c, ' ELSE '' END) END as Names ) select SUBSTRING(Names, 0, Len(Names)) as Names from CTE

Alternatively you can just do it inline but you'll have to repeat your string construction a second time this way.

CASE WHEN Col1 = 1 THEN SUBSTRING( (CASE WHEN Col2=2 THEN 'a, ' ELSE '' END) + (CASE WHEN Col3=2 THEN 'b, ' ELSE '' END) + (CASE WHEN Col4=2 THEN 'c, ' ELSE '' END) ,0, LEN( (CASE WHEN Col2=2 THEN 'a, ' ELSE '' END) + (CASE WHEN Col3=2 THEN 'b, ' ELSE '' END) + (CASE WHEN Col4=2 THEN 'c, ' ELSE '' END) )) END as Names

Just to explain what I'm doing here:

SUBSTRING (string, start point, end point) - returns contents of string from start point (0 is beginning) and the end point

LEN (string) - returns the amount of characters in the string

By passing 0 as the start and the LEN() of the string as the last parameter, it will return the full string missing the last character (because the index starts at zero). Usually you would have to put LEN() - 1 but apparently LEN does not count the last empty character.

How do I remove a trailing comma when an alias is involved?

1 Answers1