Need help on using LIKE

[newphpcoder]

Hi,

Good day!

I encountered difficulties in checking of the data is like on the data from my table.

I have table

wms_picking

and I have lot_number field and value = 'LO130318001-LO130318002' and item_code = 'MAT-CHE-0010'
then I have variable:

$lot_number = 'LO130318001LO130318002';
$mat_code = 'MAT-CHE-0010';

I created query:

select s.sr_id, p.item_code, p.lot_number from sr_chemicals AS s JOIN wms_picking AS p ON (s.sr_id = p.sr_id)
WHERE p.item_code = '$mat_code' and p.lot_number LIKE '%$lot_number%';


select s.sr_id, p.item_code, p.lot_number from sr_chemicals AS s JOIN wms_picking AS p ON (s.sr_id = p.sr_id)
WHERE p.item_code = 'MAT-CHE-0010' and p.lot_number LIKE '%LO130318001LO130318002%';

and I got no result, but I just want to get the result of lot_number field field and value = 'LO130318001-LO130318002' and item_code = 'MAT-CHE-0010'.

Any help is highly appreciated.


Thank you so much.

[NogDog]

'LO130318001LO130318002' is not like 'LO130318001-LO130318002', although 'LO130318001%LO130318002' is like 'LO130318001-LO130318002'.

What the ultimate solution should be here is difficult to say for sure, without perhaps a better understanding of the "rules" that would explain what the expected format/structure of the values to compared will be (e.g.: are they of a known fixed length, will the hyphen always be in the same place, etc.?).

[newphpcoder]

Sorry, but the hypen is only added to save the lot_number.

Thank you

[NogDog]

That still doesn't really explain to me what the naming rules are for the value in the DB versus the value you want to compare to it. The most efficient search for the current DB schema and value would be to simply insert the hyphen into the string to be compared to the DB value. But this would assume that there is something standard/fixed about the value so that you could determine exactly where to stick the hyphen.

For example, if it's always before the "LO", then you could use a fairly simple regex:

$lot_number_to_use_in_query = preg_replace('/(?<!^)LO/', '-LO', $lot_number); 

If instead the best algorithm is that it's a fixed length string and the hyphen always goes in a specific location, then substr_replace() could be your friend.

[newphpcoder]

I got the solution:

instead of using LIKE, it works when I used this :
AND REPLACE(p.lot_number,'-','') = '%$lot_number%';

Thank you