Including local variable in concat string in MySQL

Question

I'm looking to take a specified string and query a table where a concat of 2 fields is equal to the string.

set @fab = "36013-601301-11";
set @job = substring_index(@fab, '-', 1);
set @fabnumba = trim(leading LEFT(@fab,char_length(@job)+1) from @fab);

select * from (select JobNumber, concat(JobNumber, '-', LotNumber) as bomfab from qiw_powerbi) base
where bomfab LIKE concat(@job,"-", @fabnumba)

If I try the following it fails:

WHERE bombfab LIKE "36013-601301-11"

However, this attempt works:

WHERE bombfab LIKE "36013-%601301-11"

How can I concat() with the variables @job and @fabnumba to do this?

Edit your post and show some sample (not live values) of the data you are trying to retrieve out to clarify the context of your query. Use spaces and not tabs when posting sample data, structures, etc. — DRapp
– DRapp, Commented Oct 18, 2016 at 17:37
Updated with an example of what works and an example of what doesn't, to hopefully clarify better. — El Tigre
– El Tigre, Commented Oct 18, 2016 at 17:45

CLAbeel · Accepted Answer · 2016-10-18 17:59:18Z

6

Are you sure that the LotNumber values from qiw_powerbi are what you are expecting? They don't have any leading spaces?

What happens if you try adding a TRIM function to LotNumber:

select * from (select JobNumber, concat(JobNumber, '-', TRIM(LotNumber)) as bomfab from qiw_powerbi) base
where bomfab LIKE concat(@job,"-", @fabnumba)

answered Oct 18, 2016 at 17:59

CLAbeel

1,08814 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

El Tigre Over a year ago

Aha! With the SQL front-end I'm using it strips out the leading blank characters, so I forgot all about that. Thank you very much for the reminder!

CLAbeel Over a year ago

Did that solve the problem? Were there leading blanks?

El Tigre Over a year ago

I could actually use:

El Tigre Over a year ago

I can now use WHERE bomfab LIKE @fab with the TRIM. Thanks again!

Collectives™ on Stack Overflow

Including local variable in concat string in MySQL

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related