CODEWITHSUNDEEP
mysqlregex
Calvin
Calvin

Reputation: 710

MySQL Regex - Having issues

I'm having an issue with converting some regex to work with a MySQL query...

First of all, here is a http://www.regexr.com/ link... http://regexr.com/3c02t

As you can see it's working how I want. The 6812 is a user-supplied string, and I want to match all instances that end with a . and 3 numerical characters.

However, when I try to port this over to a MySQL query, I'm running into a brick wall. I'm try to do this query, and I'm not having any luck.

SELECT 
    id
FROM
    mytable 
WHERE
    summary REGEXP '^6812[.][0-9]{3}$' 
OR 
    detail REGEXP '^6812[.][0-9]{3}$'

I've looked around for the past hour and tried a bunch of different things, but I'm not having any luck here. Could someone possibly point me in the right direction on what I may be doing wrong?

Upvotes: 0

Views: 37

Answers (3)

chris85
chris85

Reputation: 23880

^ and $ are anchors they require the string be a full match. Specifically ^ is the start and $ is the end.

In your provided example you aren't using these.

(6812)\.+[0-9][0-9][0-9]

that should actually be

(6812)\.[0-9]{3}([^0-9]|$)

So your example

Replace (24) 4569.000 with (24) 6812.000

blah blah blah 6812.000.

Should return true. We are checking after your value, a period, then 3 numbers and a non-number or the end of the string/column.

Regex101 Demo: https://regex101.com/r/sK0nN5/1

Upvotes: 2

Alex
Alex

Reputation: 17289

I am not sure what your problem is, but if you need to check only las 4 characters to match you can remove ^ symbol from your reg expression:

SELECT 
    id
FROM
    mytable 
WHERE
    summary REGEXP '\.[0-9]{3}$' 
OR 
    detail REGEXP '\.[0-9]{3}$'

http://sqlfiddle.com/#!9/9eecb7d/25641

Upvotes: 0

zedfoxus
zedfoxus

Reputation: 37119

Sometimes funny things may happen when you have a decimal number. Here's the suggestion.

create table test (id decimal(10,4));
insert into test values (6812.123), (6812.1234);

select * from test
where id regexp '^6812[.period.][0-9]{3}0*$'

Result:
6812.1230

select * from test
where id regexp '^6812[.period.][0-9]{3}$'

Result: nothing

The last query results in nothing is because decimal(10,4) results in 4 digit output after the decimal. So, ^6812.123$ is unmatched because the number actually is 6812.1230

Therefore, if you are running in a situation like this, use a 0*$ to match zeros at the end.

Upvotes: 0

Related Questions