How to use comparison operator for numeric string in MySQL?

To use comparison operator for numeric string, use the substring() method. Let us first create a table −

mysql> create table DemoTable1881    (    UserId int,    UserEducationGap varchar(20)    ); Query OK, 0 rows affected (0.00 sec)

Insert some records in the table using insert command −

mysql> insert into DemoTable1881 values(101,'5-9'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1881 values(102,'2-4'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1881 values(103,'4-8'); Query OK, 1 row affected (0.00 sec) mysql> insert into DemoTable1881 values(104,'7-12'); Query OK, 1 row affected (0.00 sec)

Display all records from the table using select statement −

mysql> select * from DemoTable1881;

This will produce the following output −

+--------+------------------+ | UserId | UserEducationGap | +--------+------------------+ |    101 | 5-9              | |    102 | 2-4              | |    103 | 4-8              | |    104 | 7-12             | +--------+------------------+ 4 rows in set (0.00 sec)

Here is the query to use comparison operator for numeric string in MySQL 

mysql> select * from DemoTable1881 where cast(substring(UserEducationGap, 1, instr(UserEducationGap, '-')-1) as signed) >= 4;

This will produce the following output −

+--------+------------------+ | UserId | UserEducationGap | +--------+------------------+ |    101 | 5-9              | |    103 | 4-8              | |    104 | 7-12             | +--------+------------------+ 3 rows in set (0.00 sec)