I've found an odd problem in queries.
I have a lot of data regarding vehicle speeds in a survey. All the data is in the form: xx/yy, for example 43/55 means that vehicle was clocked at 43 miles per hour in a 55 miles per hour zone. 80/55 means we have a serious speed demon, going 80 in a 55 zone.
I need to be able to pull out data depending on speeds in relation to the speed limit, for example, find out how many are less than 55/55 or 45/45, or how many are more than 5 miles over (like 61/55 or 32/25).
I know how to use regular expressions and can SELECT only tables with the data in this form (many are easier and have an extra column filled in with the speed in relation to the speed limit), so I can pull out all the records with the formatting I'm discussing.
Is there any way, within a SELECT, to pull out the numbers and subtract them? For instance, if a record has "45/55", I'd like to be able to separate them into 45 and 55 and subtract them, like 45-55 to get -10, which tells me the speed is 10 miles under the limit.
I can program around this with Perl (another topic, which I'm not raising here), but that means selecting data, putting it into a table and going through each record individually, which will take MUCH longer than doing it by a SELECT statement within MySQL.
Thanks for any help or ideas.
Hal
First, you really ought to separate your data into two columns, say clocked and speed_limit. Squeezing two values into one column is the cause of your problem.
That said, you can do what you want with string functions. See <http://www.mysql.com/doc/en/String_functions.html>. You don't give the name of the table or the column, so I'll pretend they are 'speeds' and 'ratio', respectively. You need to find the '/', then separate accordingly and subtract. Like this:
SELECT LEFT(ratio,INSTR(ratio,'/')-1) - SUBSTRING(ratio,INSTR(ratio,'/')+1) FROM speeds;
Alternatively, you could fix the table. Something like:
ALTER TABLE speeds ADD clocked INT, speed_limit INT; UPDATE speeds SET clocked = LEFT(ratio,INSTR(ratio,'/')-1), speed_limit = SUBSTRING(ratio,INSTR(ratio,'/')+1);
Then your query becomes
SELECT clocked - speed_limit FROM speeds;
Hope that helps.
Michael
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
