Mysql REGEXP how to do an exact match

Question

hi,

i have a notes column which contains text and has an id within the text, something like "some random text (actvityid - 1234)"

i need to pull out the id 1234 in this case and update the activityid column within the same table.

my query looks like this

"UPDATE table_name SET activityId = {$f['activityId']} WHERE notes REGEXP '{$f['activityId']}' "

the problem with this is if $f['activityId'] is 34 or 123 for example it still updates the activityid column with that value. How can i do an exact match on "1234" and update only if it matches the whole string, here "1234".

Many thanks.

Answer 1

WHERE notes REGEXP CONCAT('(actvityid - ', {$f['activityId']}, ')')

or

WHERE notes REGEXP '[[:<:]]{$f['activityId']}[[:>:]]'

[[:<:]] and [[:>:]] stands for word boundaries.

Answer 2

No need to use CONCAT if variable is passed from PHP, and no need to use REGEXP if you match exact string without special characters

WHERE notes LIKE '%(actvityid - {$f['activityId']})%'