Regex in mysql to match digits with leading 0

Question

I am trying to match in sql using the following:

SELECT * FROM mytable WHERE vendor_id REGEXP '^0\d+'

However, it returns no results. Doing vendor_id REGEX '^0' works though. How do I search for 0 followed by other digits?

What is the datatype of `vendor_id`?? – Rick James Jan 19 '18 at 15:47 — Rick James, Jan 19 '18 at 15:47
Are you asking how to _search_, or how to _extract_? – Rick James Jan 19 '18 at 15:48 — Rick James, Jan 19 '18 at 15:48

score 1 · Accepted Answer · answered Jan 18 '18 at 20:00

MySQL regex uses POSIX Extended Regular Expression (ERE) that doesn't know about PCRE regex property classes such as \d, \w etc. You can use [0-9] or POSIX class [[:digit:]] as:

SELECT * FROM mytable WHERE vendor_id REGEXP '^0[0-9]+'

OR else:

SELECT * FROM mytable WHERE vendor_id REGEXP '^0[[:digit:]]+'

Regex in mysql to match digits with leading 0

1 Answers1