Select values that begin with a number

Question

I have a table with a column containing data that begin with numbers too, on MySQL

How can I select the rows that begin only with a number?

Same general sentiment as this thread on how to [Detect if value is number in MySQL](https://stackoverflow.com/q/5064977/1366033), but just restricted to the first char — KyleMit, Dec 22 '17 at 19:23

Sarfraz · Accepted Answer · 2010-12-22T17:47:43.653

47

SELECT * FROM YourTable WHERE YourColumn regexp '^[0-9]+'

edited Dec 22 '10 at 17:47

answered Dec 22 '10 at 17:40

Sarfraz

377,238
77
533
578

1

+1 for `REGEXP` (I don't think `LIKE` supports RegEx (as @RedFilter) has implied). Though, I would probably use `'^[0-9]+'` as a pattern myself. – Brad Christie Dec 22 '10 at 17:44
1

Do I need to add any assembly or something to use REGEXP in MS SQL server? – Magendran V Apr 13 '15 at 09:33
Hey can you post the sqlite version for the query above because regexp in not available in some android devices. Thanks in advance – Redone Jun 15 '15 at 07:56
you may also use rlike instead of like – malyy Jun 08 '16 at 11:30
whyn not `'^[0-9].+'`? – Alex78191 Jul 25 '19 at 17:07

ypercubeᵀᴹ · Answer 2 · 2013-05-12T23:24:15.303

13

Yet another way:

WHERE LEFT(columnName,1) IN ('0','1','2','3','4','5','6','7','8','9')

and with common charsets and collations, this would work and use an index on the column:

WHERE columnName >= '0' AND columnName < ':'

edited May 12 '13 at 23:24

answered May 12 '13 at 23:16

ypercubeᵀᴹ

113,259
19
174
235

codaddict · Answer 3 · 2010-12-22T17:50:50.827

You can do:

SELECT *
FROM MyTable
WHERE MyColumn REGEXP '^[0-9]';

The regular expression used is ^[0-9].

^    - Start anchor, used to ensure the pattern matches start of the string.
[    - Start of character class.
0-9  - Any digit
]    - End of character class

Effectively we are trying to select those values in the column that begin with a digit.

Demo:

mysql> select * from tab;
+-------+
| col   |
+-------+
| 1foo  |
| foo   |
| 10foo |
| foo10 |
+-------+
4 rows in set (0.00 sec)

mysql> select * from tab where col regexp '^[0-9]';
+-------+
| col   |
+-------+
| 1foo  |
| 10foo |
+-------+
2 rows in set (0.00 sec)

score 6 · Answer 4 · answered May 12 '13 at 22:52

6

also

SELECT * FROM YourTable
WHERE YourColumn LIKE '[0-9]%';

answered May 12 '13 at 22:52

Lukas Greso

472
4
6

2

`SELECT * FROM \`artists\` WHERE \`artist\` LIKE '[0-9]%'` returns empty. – akinuri Jul 06 '16 at 23:22
2

MySQL's `like` does not support such patterns. You have to use `regexp` for that. e.g.: `SELECT * FROM foo WHERE bar REGEXP '^[0-9]'`. https://dev.mysql.com/doc/refman/5.7/en/pattern-matching.html – Tristan Jahier Mar 22 '17 at 19:31

score 2 · Answer 5 · edited Jun 08 '16 at 12:05

2

SELECT * FROM TABLE T
WHERE T.COLUMNNAME REGEXP '^[0-9]';

Another answer is:

SELECT * FROM TABLE T
WHERE T.COLUMNNAME RLIKE '^[0-9]';

edited Jun 08 '16 at 12:05

Vasily Kabunov

6,511
13
49
53

answered Jun 08 '16 at 11:27

Vaibhav Kumar

544
5
17

"`RLIKE is a synonym for REGEXP`" – jave.web Apr 06 '17 at 03:54

Select values that begin with a number

5 Answers5

Linked

Related