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?

Answer 1

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)