I want to use a regualr expression in a mysql search that macth a string and NOT the same string followed by a dot . character. As an example i want to match the product code 3.4.5.1 but not 3.4.5.1.4 I have a workaround using a double like :

SELECT *  from mytable  where myclolumn like '%3.4.5.1%' AND mycolumn NOT like '%3.4.5.1.%'....

Not a very elegant solution. So I was trying to use REGEXP_LIKE(mycolumn,'(3.4.5.1)[^\.]')

In fact I have tested that pattern and works nicely in php and even online : https://regexr.com/66olc but seems that regex works in their own way in MYSQL because I obtain an empty search result. What I'm doing wrong?

CodePudding user response：

You can use

([^0-9]|^)3\.4\.5\.1([^.0-9]|$)

See the regex demo. Details:

• ([^0-9]|^) - a non-digit char ([^0-9]) or (|) start of string (^)
• 3\.4\.5\.1 - 3.4.5.1 literal string (dots in regex must be escaped with a literal backslash to match a . char, else, . matches any single char)
• ([^.0-9]|$) - any char other than a . and digit ([^.0-9]), or (|) end of string ($).