Get only digits using regexp

Question

I have in table column pnum_s

I need get only that rows, which value in column pnum_s is exactly 10 symbol and all these symbols are only digits

what query must write for this?

I am trying

SELECT * FROM mytable WHERE pnum_s REGEXP '^\d+$'

But this not returns 0 rows

Possibly, I'm not familiar with how MySQL does Regular Expressions but according to (http://www.regular-expressions.info/mysql.html) backslashes need to be escaped. OP issue may be you should use `\$` rather then just `$`. — David Starkey, Jun 04 '13 at 15:58

score 23 · Accepted Answer · answered Jun 04 '13 at 16:02

23

The pattern you are looking for is either '^[0-9]{10}$' or '^[[:digit:]]{10}$'.

Everything is in the manual.

answered Jun 04 '13 at 16:02

RandomSeed

Can I use where column_name REGEXP '[0-9]' it is also working – Brij May 22 '18 at 06:26
@RandomSeed, Is there any way to restrict alphanumeric value – er.irfankhan11 Nov 03 '18 at 07:22

score 7 · Answer 2 · edited May 18 '17 at 06:55

7

I think with Mysql you'll want something like this:

^[[:digit:]]{10}$

edited May 18 '17 at 06:55

Jin Kwon

answered Jun 04 '13 at 15:57

danpaq

score 6 · Answer 3 · edited May 18 '17 at 06:55

6

Check out the reference page.

What you're looking for is this:

SELECT * FROM mytable WHERE pnum_s REGEXP '^[[:digit:]]{10}$';

edited May 18 '17 at 06:55

Jin Kwon

answered Jun 04 '13 at 16:07

rollcona

score 2 · Answer 4 · answered Jun 04 '13 at 15:57

2

try this pattern:

'^[0-9]{10}$'

answered Jun 04 '13 at 15:57

4 Answers4