How to use comparison operator for numeric string

Question

I have an employee table which has field like experience with varchar type, this field combines both total year and total month separated by dash(-), so I have to filter experience by year who has more than 3 years experience.

my table structure:

So now I have to get more than 3 years experienced id's. I tried like below,

SELECT * FROM employee WHERE experience LIKE '>=3%';

I know comparison operator will not support for string but I do not have any new solution for that, Is there any solution?

Answer 1

better answer based on converting to number:

select * from employee where cast(substring(experience, 1, instr(experience, '-')-1) as signed) >= 3;

mysql> select cast(substring('11-3', 1, instr('11-3', '-')-1) as signed);
+------------------------------------------------------------+
| cast(substring('11-3', 1, instr('11-3', '-')-1) as signed) |
+------------------------------------------------------------+
|                                                         11 |
+------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select cast(substring('11-3', 1, instr('11-3', '-')-1) as signed) > 3;
+----------------------------------------------------------------+
| cast(substring('11-3', 1, instr('11-3', '-')-1) as signed) > 3 |
+----------------------------------------------------------------+
|                                                              1 |
+----------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select cast(substring('11-3', 1, instr('11-3', '-')-1) as signed) >= 3;
+-----------------------------------------------------------------+
| cast(substring('11-3', 1, instr('11-3', '-')-1) as signed) >= 3 |
+-----------------------------------------------------------------+
|                                                               1 |
+-----------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select cast(substring('3-0', 1, instr('3-0', '-')-1) as signed) >= 3;
+---------------------------------------------------------------+
| cast(substring('3-0', 1, instr('3-0', '-')-1) as signed) >= 3 |
+---------------------------------------------------------------+
|                                                             1 |
+---------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select cast(substring('2-11', 1, instr('2-11', '-')-1) as signed) >= 3;
+-----------------------------------------------------------------+
| cast(substring('2-11', 1, instr('2-11', '-')-1) as signed) >= 3 |
+-----------------------------------------------------------------+
|                                                               0 |
+-----------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select cast(substring('22-11', 1, instr('22-11', '-')-1) as signed) >= 3; 
+-------------------------------------------------------------------+
| cast(substring('22-11', 1, instr('22-11', '-')-1) as signed) >= 3 |
+-------------------------------------------------------------------+
|                                                                 1 |
+-------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select cast(substring('11-0', 1, instr('11-0', '-')-1) as signed) >= 3; 
+-----------------------------------------------------------------+
| cast(substring('11-0', 1, instr('11-0', '-')-1) as signed) >= 3 |
+-----------------------------------------------------------------+
|                                                               1 |
+-----------------------------------------------------------------+
1 row in set (0.00 sec)

unsigned would probably be better for readability. wouldn't likely have any affect on the query.

Answer 2

Select * from main where cast(split_str(experience, '-', 1) as unsigned) >= 3;

split_str (it is an user function : http://blog.fedecarg.com/2009/02/22/mysql-split-string-function/, not by default in mysql) will split your column with its seperator and cast will turn your string into an int unsigned, then you can test it. Else if one day you want to test months and years, like all experience above 3 year and 10 months, you can convert your value in decimal (this also work for above test) :

Select * from main where cast(replace(experience, '-', '.') As decimal(5,2)) > 3.10;

I might have done some typos (typing on my smartphone), but you have got the idea.

Answer 3

how about strcmp for this simple case? it wouldn't work if you needed both year and month though.

mysql> select strcmp('3-','3-1');
+--------------------+
| strcmp('3-','3-1') |
+--------------------+
|                 -1 |
+--------------------+
1 row in set (0.00 sec)

mysql> select strcmp('3-','3-9');
+--------------------+
| strcmp('3-','3-9') |
+--------------------+
|                 -1 |
+--------------------+
1 row in set (0.00 sec)

mysql> select strcmp('3-','2-9');
+--------------------+
| strcmp('3-','2-9') |
+--------------------+
|                  1 |
+--------------------+
1 row in set (0.00 sec)

you use this in a query like so:

mysql> select * from main where strcmp('ab', url) = 1;
+----+---------------+
| id | url           |
+----+---------------+
|  1 | a.com         |
|  2 | aa.com        |
|  3 | aaa.com       |
|  4 | aachen.com    |
|  5 | aah.com       |
|  6 | aaliyah.com   |
|  7 | aaliyahs.com  |
|  8 | aardvark.com  |
|  9 | aardvarks.com |
| 10 | aaron.com     |
| 11 | aas.com       |
+----+---------------+
11 rows in set (0.02 sec)

so in your case you'd want something like select * from employee where strcmp('3-', experience) = -1;

if I were the db admin, and if the experience mentioned means years at this job, I'd be wishing I had just used timestamps for start and end, so it could easily be calculated. otherwise every month a batch job will have to update the field.

see http://dev.mysql.com/doc/refman/5.7/en/string-comparison-functions.html#function_strcmp

[UPDATE: fixed for cases from 11- to 29-, by checking if the years part of the string is greater than a single digit]

select * from employee where strcmp('3-', experience) = -1 or instr(experience, '-') > 2;