Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Trimming off last character if it is a comma SQL

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
like image 664
jjohnson Avatar asked Mar 17 '23 12:03

jjohnson


2 Answers

Try this

IF RIGHT(RTRIM(@MASTR),1) = ','
BEGIN
-- Chop off the end character
SET @MASTR = LEFT(@String, LEN(@MASTR) - 1)
END
SELECT @MASTR
like image 165
HaveNoDisplayName Avatar answered Mar 31 '23 13:03

HaveNoDisplayName


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'
like image 40
Adam Miller Avatar answered Mar 31 '23 14:03

Adam Miller