How to check the column values have string or digits in MySQL?

If you want only the string values, then use the below syntax −

select *from yourTableName where yourColumnName NOT regexp '^[0-9]+$';

If you want only the digit, then use the below syntax −

select *from yourTableName where yourColumnName regexp '^[0-9]+$';

Let us first create a table −

mysql> create table DemoTable(
   Id varchar(100)
);
Query OK, 0 rows affected (0.49 sec)

Insert some records in the table using insert command −

mysql> insert into DemoTable values('1000');
Query OK, 1 row affected (0.16 sec)
mysql> insert into DemoTable values('John');
Query OK, 1 row affected (0.10 sec)
mysql> insert into DemoTable values('Carol_Smith');
Query OK, 1 row affected (0.15 sec)
mysql> insert into DemoTable values('2000');
Query OK, 1 row affected (0.10 sec)

Display all records from the table using select statement −

mysql> select *from DemoTable;

This will produce the following output −

+-------------+
| Id          |
+-------------+
| 1000        |
| John        |
| Carol_Smith |
| 2000        |
+-------------+
4 rows in set (0.00 sec)

CASE 1 − Following is the query to get only the string value −

mysql> select *from DemoTable where Id NOT regexp '^[0-9]+$';

This will produce the following output displaying only the string column values −

+-------------+
| Id          |
+-------------+
| John        |
| Carol_Smith |
+-------------+
2 rows in set (0.00 sec)

CASE 2 − Following is the query to get only the digits −

mysql> select *from DemoTable where Id regexp '^[0-9]+$';

This will produce the following output displaying only the digit column values −

+------+
| Id   |
+------+
| 1000 |
| 2000 |
+------+
2 rows in set (0.00 sec)

AmitDiwan

Updated on: 2019-10-04T07:22:19+05:30

1K+ Views

Kickstart Your Career

Get certified by completing the course

Get Started