-1

I am trying to use defined variables in MySQL Workbench. I am using version 6.3 of Workbench. I am trying to run ad hoc queries that can have a value or multiple values:

If I have a single value, the query runs okay

set @Zdept_Id="548"

and then

Select *
from employees
where dept_Id in (@ZDept_Id)

but if I want to execute this for multiple departments, the query only returns values for the first number

set @Zdept_Id="548,221,1973,905"

and then

Select *
from employees
where dept_Id in (@ZDept_Id)

Dept_Id is an integer.

PS. These are reports that I run once a month, so automating them is not an option due to cost/benefit.

  • 1
    A variable value is ONE value. So you obtain Select * from employees where dept_Id in ('548,221,1973,905'), not Select * from employees where dept_Id in (548,221,1973,905) or Select * from employees where dept_Id in ('548','221','1973','905'). – Akina Jan 14 at 10:38
1

I hope you are looking for FIND_IN_SET().

SELECT *
FROM employees 
WHERE FIND_IN_SET(dept_Id, @ZDept_Id);
  • 1
    @Vérace You're not right. fiddle. Read the sentense which you citate until its end. – Akina Jan 14 at 10:43
  • @Akina - thanks for pointing out my error and my apologies to Arulkumar! My error stems from the fact that there is a "gotcha" in this - if you have @var = "145, 245, 345) - note spaces after commas - I normally put these in to make my SQL more readable), the query fails. Only when the @var string is set to "145,245,345)" (no spaces, does it work (see fiddle). – Vérace Jan 14 at 10:57
  • @Vérace note spaces after commas Of course! FIND_IN_SET is string function, not numeric one, so space matters. So the value to be searched for is converted to string, not parsed element to number. PS. Excess beauty in the code is evil. – Akina Jan 14 at 11:03
  • @Vérace In Russian there exists a word which means both "to take away" and "to rob", and the difference is in context only... – Akina Jan 14 at 13:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.