MySQL - 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 ''; 
";

Answer 1

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.

Answer 2

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.