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?

Solution 1

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

Solution 2

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)

Solution 3

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 < ':'

Solution 4

also

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

Solution 5

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

Another answer is:

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

View more solutions

Comments

[KyleMit]

Same general sentiment as this thread on how to Detect if value is number in MySQL, but just restricted to the first char

[Brad Christie]

+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.

[Magendran V]

Do I need to add any assembly or something to use REGEXP in MS SQL server?

[Redone]

Hey can you post the sqlite version for the query above because regexp in not available in some android devices. Thanks in advance

[malyy]

you may also use rlike instead of like

[akinuri]

SELECT * FROM `artists` WHERE `artist` LIKE '[0-9]%' returns empty.

[Tristan Jahier]

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]'. dev.mysql.com/doc/refman/5.7/en/pattern-matching.html

[jave.web]

"RLIKE is a synonym for REGEXP"

[Alex78191]

whyn not '^[0-9].+'?