CODEWITHSUNDEEP
mysqlsqlconcatenationcase-when
SmoothPeaches
SmoothPeaches

Reputation: 35

MYSQL case when with concatenation

I cant seem to get the syntax correct when I'm trying to add conditions that must be satisfied before concatenating multiple inputs I will leave code below but essentially CASE when 'Condition'THEN concatenate these strings + columns THEN give me X ELSE 0 essentially like any other case when statement but with concat. I have played around with the IF statement but that hasn't been successful either ( I primarily use MYSQL )

the reason I am using concat twice is the info is in two separate tables so I need to use the || to produce the output I need but I want 10 case when conditions where it will shot out that concatenation based on if one of those conditions is true

CASE WHEN 'specific condition' THEN concat('string',employee2.firstname) || concat('/employerid/',employee.lastname) ELSE 'Null')
WHEN ....
WHEN.....
WHEN ....END) as employee_ID

Upvotes: 0

Views: 5093

Answers (1)

forpas
forpas

Reputation: 164184

The operator || is the logical OR operator in MySql and not a concatenation operator (unless PIPES_AS_CONCAT SQL mode is enabled).

the reason I am using concat twice is the info is in two separate tables

If what you want is to concatenate all 4 values then all you need is access to both tables from the CASE expression and then there is no reason to use concat() twice .

Concat() can take multiple parameters, so instead of:

concat('string',employee2.firstname) || concat('/employerid/',employee.lastname)

do this:

concat('string',employee2.firstname, '/employerid/',employee.lastname)


Note: 'Null' is not NULL, it's a string literal.
So do something like this:

CASE 
  WHEN 'specific condition' THEN concat('string',employee2.firstname,'/employerid/',employee.lastname) 
  WHEN ....
  WHEN.....
  WHEN ....
END as employee_ID

The ELSE part is not needed if you want it just to return NULL.

Upvotes: 3

Related Questions