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 :
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?
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