Mysql REGEXP select numbers starting with given digit(s)

Question

my table has a column with comma-separated (and eventually a space, too) numbers; those numbers can have from five to twelve digits.

9645811, 9646011,9645911, 9646111

or

41031, 41027, 559645811, 5501006009

I need to select the rows with that column containing a number STARTING with given digits. In the above examples, only the first has to be selected. What I've tried so far:

SELECT myfield FROM mytable
WHERE myfield REGEXP ('(^|[,\s]+)(96458[\d]*)([,\s]*|$)');

However the query returns no results. I'd like to select only the first row, where there is a number STARTING with 96458.

Any help would be appreciated :)

your regex seems ok.

Saif
– Saif

2015-09-10 09:41:53 +00:00
Commented Sep 10, 2015 at 9:41 — Saif
– Saif, Commented Sep 10, 2015 at 9:41
WHERE SUBSTRING_INDEX(myfield ,',',1) LIKE '96458%'

Mihai
– Mihai

2015-09-10 09:53:32 +00:00
Commented Sep 10, 2015 at 9:53 — Mihai
– Mihai, Commented Sep 10, 2015 at 9:53

Wiktor Stribiżew · Accepted Answer · 2015-09-10 09:47:54Z

0

You need to use a starting word boundary [[:<:]]:

SELECT myfield FROM mytable WHERE myfield REGEXP ('[[:<:]]96458');

See the MySQL regex syntax for more details.

[[:<:]], [[:>:]]

These markers stand for word boundaries. They match the beginning and end of words, respectively.

See this SQL fiddle.

edited Sep 10, 2015 at 9:47

answered Sep 10, 2015 at 9:41

Wiktor Stribiżew

631k41 gold badges502 silver badges632 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Mysql REGEXP select numbers starting with given digit(s)

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related