I have a mysql table like following
jobno | duration
----------------
1 | 1 hr
2 | 3 hrs
3 | 1 hr 30 mins
4 | 2 hrs 15 mins
5 | 1 hr 15 min
6 | 45 mins
7 | 30 min
Is it possible to select the jobs with duration less than 2 hrs ? After spending a lot of time in google i found that there is regexp function in mysql. But is that applicable in this case? Am new to mysql. Pls help me guys.
select * from TableName where duration NOT REGEXP '^([^1]|[1-9][0-9]+) hr.*'
Try this.This will work.See here
For less than 3 you can use
select * from TableName where duration REGEXP '^([1-2] hr|[0-9]+ min).*'
http://www.sqlfiddle.com/#!2/ba4de/12/0
Similarly for 4 use [1-3] and so on.
Seeing your duration column format. why cant you just use like:
select * from yourTableName where duration like '1 hr%' or duration NOT like '%hr%';
or if you are looking for flexibility like below 4 hours try this:
select * from yourTableName
where duration REGEXP '^[1-3].*$' or duration not like "%hr%";
Do not attempt to solve the problem in SQL, the language is not rich enough. Instead, use Perl or PHP or some language that has a good REGEXP library.
Add a new column.
Fetch the rows, convert them in your application language, then UPDATE the new column.
Drop the existing column -- or keep it for human readability.
Yes, probably. It will be painful and is really over-engineering a solution here.
The general purpose of a database is to speed up access and, in general, regular expressions are expensive. Another problem here is that there might not be a promise about how these time strings are stored. In the example that you give, you need to parse out the number value to decide if "hr" or "hrs" should be expected next.
I would suggest instead storing the durations in seconds (as a MySQL INT). You can then quickly and easily search and sort on that INT:
jobno | duration
-------------------
1 | 3600
2 | 10800
3 | 5400
4 | 8100
5 | 4500
6 | 2700
7 | 1800
The "less than two hours" query would then look like this:
SELECT `jobno` FROM `jobs` WHERE `duration` < 7200;
You can try this (without using REGEX):
select * from TableName
where (duration not like '%hr%')
OR (duration like '1 hr%')
Result:
JOBNO DURATION
1 1 hr
3 1 hr 30 mins
5 1 hr 15 min
6 45 mins
7 30 min
Sample result in SQL Fiddle.
Explanation:
The first condition in WHERE clause will fetch the records with duration doesn't contain the word 'hr' (which will be the minutes only). Second condition is to fetch duration starting with 1 hr, which will be the duration between 1hr-2hr.
EDIT:
To make it more flexible, you can do this:
select * from TableName
where (duration not like '%hr%')
OR (CAST(SUBSTRING(duration,1,2) AS UNSIGNED) < 4)
Result:
JOBNO DURATION
1 1 hr
2 3 hrs
3 1 hr 30 mins
4 2 hrs 15 mins
5 1 hr 15 min
6 45 mins
7 30 min
Sample result in SQL Fiddle.
