151291 151291 - 5 years ago 126
SQL Question

How to use comparison operator for numeric string in mysql?

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

my table structure :

enter image description here

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

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


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

Answer Source

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

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download