CODEWITHSUNDEEP
mysqlsqlregex
k00k
k00k

Reputation: 17573

REGEXP in a WHERE statement?

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

Upvotes: 0

Views: 411

Answers (2)

ty812
ty812

Reputation: 3323

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.

Upvotes: 3

James McNellis
James McNellis

Reputation: 355049

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.

Upvotes: 5

Related Questions