REGEXP in a WHERE statement?

Question

I have a table with a field called 'user_car'. It consists of a cat'd underscore separated value (user's id _ car's id)

user_car          rating
-----------------------------
11_56748           4
13_23939           2
1_56748            1
2001_56748         5
163_23939          1

I need to get the average rating for any "car". In my example table, there are only 2 cars listed: 56748 and 23939. So say I want to get the average rating for the car: 56748, so far I have this SQL, but I need the correct regex. If I'm totally off-base, let me know. Thanks!

$sql = "
    SELECT AVG 'rating' FROM 'car_ratings'
    WHERE 'user_car' REGEXP ''; 
";

This is a bad idea. You would be much better off splitting user_car into user_id and car_id. — James McNellis
– James McNellis, Commented Oct 26, 2009 at 18:46
Agreed. Sometimes we have to make do with the data structures we're given. All part of the process in this case. — k00k
– k00k, Commented Oct 26, 2009 at 18:51

James McNellis · Accepted Answer · 2009-10-26 18:49:29Z

5

You can extract the car id using:

substring(user_car from (locate('_', user_car) + 1))

this will allow you to do:

select   substring(user_car from (locate('_', user_car) + 1)) as car_id,
         avg(rating)
from     car_ratings
group by car_id

But, this is a bad idea. You would be much better off splitting user_car into user_id and car_id.

answered Oct 26, 2009 at 18:49

James McNellis

357k76 gold badges934 silver badges983 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

ty812 Over a year ago

How do you know (s)he uses PHP to access the database?

James McNellis Over a year ago

I don't; did I say something about PHP?

ty812 Over a year ago

need more sleep, my fault, sorry -_-

OMG Ponies Over a year ago

The interim solution would be to implement your query as a view, which has been supported in MySQL since 5.0.1. That is a fugly data model, no doubt...

James McNellis · Accepted Answer · 2009-10-26 18:52:57Z

3

I don't see why you need to use REGEXes ...

SELECT AVG(`rating`) FROM `car_ratings` WHERE `user_car` LIKE '%_56748'

Regexes are slow and can pretty easily shoot you in the foot. I learned to avoid them in MySQL whenever I could.

edited Oct 26, 2009 at 18:52

James McNellis

357k76 gold badges934 silver badges983 bronze badges

answered Oct 26, 2009 at 18:46

ty812

3,33322 silver badges37 bronze badges

4 Comments

ChssPly76 Over a year ago

+1 for LIKE. Keep in mind that you may get down-voted into oblivion for saying negative things about regexes (true though they may be) :-) Also, shouldn't AVG argument be enclosed in parenthesis?

ty812 Over a year ago

AVG in parenthesis: This happens when you copy stuff from questions :)

bobince Over a year ago

Actually LIKE probably won't be noticeably quicker than RLIKE/REGEXP in this case, as it can't left-optimise it. However, it's definitely still a plus because LIKE is ANSI SQL compliant; regexes are MySQL-specific. Also, regexes are a pile of poo. ;-)

Stefan Kendall Over a year ago

@ChessPly: regex expressions in the database are silly slow given the performance parameters placed upon databases. Saying "don't use regex in a database" is valid. Saying "don't use regex because I can't use them" isn't.

Collectives™ on Stack Overflow

REGEXP in a WHERE statement?

2 Answers 2

4 Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related