REGEXP in MySQL Return unwanted value

Question

Hi all

I have problem using REGEX in Mysql

I have oid value in database like this

id -> value

1.3.6.1.4.1 -> Value a

1.3.6.1.4.1.2499.1.1.2.1.1.1.1.1 -> Value b

1.3.6.1.4.1.2499 -> Value c

And my objecttives are 1. To get single oid & value with the specific oid that i put into sql statement 2. If no specific value then it should reverse the oid number by number until it found the newrest value

For example If i use [select id from tablename where '1.3.6.1.4.1.2499.1.1.2.1.1.1.1.1' REGEXP oid] it should return only 1.3.6.1.4.1.2499.1.1.2.1.1.1.1.1 but the above sql will return all result

If i use [select id from tablename where '1.3.6.1.4.1.24999999.5' REGEXP oid] it should return 1.3.6.1.4.1 only but it return 1.3.6.1.4.1 and 1.3.6.1.4.1.2499

If i use select id from tablename where '1.3.6.1.4.1.2499.1.1.2.1.1.1.1.100' REGEXP oid it should return 1.3.6.1.4.1.2499 only but it return all ids

I am not really familiar with this REGEXP. Can anyone help me to solve this problem. Thank you

Answer 1

With MySQL, you should use field REGEXP value, like this:

select id from tablename where oid REGEXP '1.3.6.1.4.1.2499.1.1.2.1.1.1.1.1'

. must be escaped with \
And to match an entire row, use ^ and $:

select id from tablename where oid REGEXP '^1\.3\.6\.1\.4\.1\.2499\.1\.1\.2\.1\.1\.1\.1\.1$'

I don't understand why do you use REGEXP when you can select by LIKE, because you don't search by a regular expression.