Trimming off last character if it is a comma SQL

Question

I have 8 if statements that if a letter is in a field print a word. And what to do is have a concatenated string of all the options in that field. Now my problem is how do I put an OR before the last option and take off the comma on the last option. Here is what I got so

    DECLARE @VAR1 VARCHAR(30)
DECLARE @VAR2 VARCHAR(30)
DECLARE @VAR3 VARCHAR(30)
DECLARE @VAR4 VARCHAR(30)
DECLARE @VAR5 VARCHAR(30)
DECLARE @VAR6 VARCHAR(30)
DECLARE @VAR7 VARCHAR(30)
DECLARE @VAR8 VARCHAR(30)
DECLARE @MASTR VARCHAR(90)
DECLARE @PATSTR VARCHAR(30)

SET @VAR1 = ''
SET @VAR2 = ''
SET @VAR3 = ''
SET @VAR4 = ''
SET @VAR5 = ''
SET @VAR6 = ''
SET @VAR7 = ''
SET @VAR8 = ''
SET @MASTR = ''
SET @PATSTR = (SELECT EMAIL FROM CLIENT
                WHERE CLIENT_KEY = 5)
IF @PATSTR LIKE '%a%' PRINT @MASTR + 'Cash, '
ELSE PRINT ''
IF @PATSTR LIKE '%b%' PRINT @MASTR + 'Check, '
ELSE PRINT ''
IF @PATSTR LIKE '%c%' PRINT @MASTR + 'Money Order, '
ELSE PRINT ''
IF @PATSTR LIKE '%d%' PRINT @MASTR + 'Visa, '
ELSE PRINT ''
IF @PATSTR LIKE '%e%' PRINT @MASTR + 'Mastercard, '
ELSE PRINT ''
IF @PATSTR LIKE '%f%' PRINT @MASTR + 'Discover, '
ELSE PRINT ''
IF @PATSTR LIKE '%g%' PRINT @MASTR + 'Amex, '
ELSE PRINT ''
IF @PATSTR LIKE '%h%' PRINT @MASTR + 'Diner Club International, '
ELSE PRINT ''
PRINT @MASTR

Solution 1

Include a case statement to check if @MASTR is blank within each condition check, as following:

IF @PATSTR LIKE '%b%'
SET @MASTR = @MASTR
  + CASE WHEN LEN(@MASTR) > 0 THEN ', ' ELSE '' END
  + CASE WHEN @PATSTR LIKE '%b' then 'OR ' ELSE '' END
  + 'Check'

Solution 2

Try this

IF RIGHT(RTRIM(@MASTR),1) = ','
BEGIN
-- Chop off the end character
SET @MASTR = LEFT(@String, LEN(@MASTR) - 1)
END
SELECT @MASTR

Comments

[Andriy M]

At the time when the IF statements are executed, the @MASTR variable contains an empty string. So, why then are you printing @MASTR + 'string' instead of just 'string'? Or did you mean to use assignment there instead of printing: SET @MASTR = @MASTR + '...'?

[Frank V]

You use rtrim in your initial IF but then only trim the last character. This would work in the case of my string, but not my string,

[Adam Miller]

This way you don't have to worry about removing a comma after the last option; you just don't add a comma before each option unless there's already stuff before it.

[HaveNoDisplayName]

@Frank V:- I tested it, its working fine with both of the values, are you getting issue?

[jjohnson]

I like your suggestion @Adam Miller but for some reason everything is showing up without a comma.

[Adam Miller]

Which kind of SQL are you using? LEN(@MASTR) should be returning the length of @MASTR in characters.

[Adam Miller]

Oh, wait. I think I see. I edited my suggestion to change 'PRINT' to 'SET'. This way, you're embedding them all in @MASTR, and then your print at the end will print the whole string.

[jjohnson]

YOU DID IT @ADAM MILLER!! THANKS!! Would there be a way to add an 'or' before the last option?

[Adam Miller]

I'm not sure what you mean by the 'or'. Do you mean you only want to show diner club if nothing else was selected?

[jjohnson]

If there was a client that had a,b,c right now it would show cash, check, money order. How do I put cash, check or money order? Or if a client has d,e,h how would I print out 'Visa, Mastercard or diners club'? Hope that helps.

[Adam Miller]

I added another 'case' to my answer that checks if the option was the last in @PATSTR. It works if they're always going to be in the order that your if statements are in...

[sotn]

@FrankV the reason it will work is related to how SQL Server handles trailing spaces. Try executing: SELECT LEN('string') and SELECT LEN('string ') and you will see that both of them returns the same value..