krishna krishna - 7 months ago 11
SQL Question

How to check that a column values has string or digits values in mysql?

I have mysql table with multiple columns, a column has string data type namely

code
and the values of the column might be digits only or mixed of string and digits e.g: one row value has
57677
and other rows column value like
2cskjf893
or
78732sdjfh
.

mysql> select * from testuser;
+------+--------------+
| id | code |
+------+--------------+
| 1 | 232 |
| 2 | adfksa121dfk |
| 3 | 12sdf |
| 4 | dasd231 |
| 5 | 897 |
+------+--------------+
5 rows in set (0.00 sec)

mysql> show create table testuser;
+----------+------------------------------------------------------------------ ---------------------------------------------------------------+
| Table | Create Table |
+----------+------------------------------------------------------------------ ---------------------------------------------------------------+
| testuser | CREATE TABLE `testuser` (
`id` int(11) DEFAULT NULL,
`code` varchar(20) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+----------+------------------------------------------------------------------ ---------------------------------------------------------------+
1 row in set (0.00 sec)

mysql>


I would like to filter out only rows which has numeric value in
code
column or filter rows which has string values in
code
column.

Answer

try this

Mysql Query to fetch only integer values from a particular column

SELECT column FROM TABLE where column NOT REGEXP '^[0-9]+$' ;

Mysql Query to fetch only string values from a particular column

SELECT column FROM TABLE where  column REGEXP '^[0-9]+$' ;