选择行包含字符串的位置

Question

I have a column named software_hardware from the table activities which could have multiple product inputs separated by a pipeline like INVENTORY| POS | or GPOS | ACCOUNTING |.

I have this query:

SELECT a.id, a.ticket_number, a.client, a.software_hardware, 
a.issues_concern, a.status, a.technical_programmer, a.date_added 
FROM activities a
WHERE a.client = '".$_POST['client']."' AND a.software_hardware LIKE '%".$arr[$i]."%'
ORDER BY date_added DESC

$arr = explode("|", $_POST['Soft_hard']);

When I select a client and a product, related info would be displayed on a div. Now my problem is, if I have POS and GPOS, I realized I couldn't use LIKE '%[input product name here]%'as this would return results that contains both POS and GPOS if I selected POS.

How can I fix this query to not display GPOS if I selected POS?

PS. I am aware of the issues with MySQL. It's what the company I'm in uses so I have no choice at the moment.

Answer 1

You can use REGEXP:

a.software_hardware REGEXP '[[:<:]]" . $arr[$i] . "[[:>:]]'

the [[:<:]] and [[:>:]] markers stand for word boundaries. So POS cannot match GPOS as there would be no word boundary at the beginning.

Note you will need to trim($arr[$i]) if it has leading or trailing whitespace.