Finding Numbers
How do I find only INT, BIGINT numbers from a column?
For e.g. in the following column I want to return 43344433 only
And not 434-433 or any other row that has dash or comma or space.
mysql> select * from ztest where name REGEXP '[[:digit:]]-';
+-----------+
| name |
+-----------+
| 434-433 |
| 4334-4433 |
+-----------+
2 rows in set (0.00 sec)
mysql> select * from ztest ;
+--------------------------+
| name |
+--------------------------+
| '004500' '00984' |
| '0304500' , '020984' |
| '000304500' , '00020984' |
| '000304500' '00020984' |
| '000304500 00020984' |
| '000304500, 00020984' |
| 434-433 |
| 4334-4433 |
| 43344433 |
+--------------------------+
9 rows in set (0.00 sec)
+ (showing there must be 1 or more digits), and add ^ and $ to indicate the start and end of the string:
ReplyDeleteSELECT * FROM zTest WHERE name REGEXP '^[[:digit:]]+$';
In other words, the name must match: start of string, 1 or more digits, end of string, and nothing else.