MySQL split string help

Databases
1

Hi Guys!

I need help with a query that i’m trying to write. In the display_name field of the jobs_locations database the values are stored like this:

Brentwood, Essex

My question is, how can I strip the value to just get “Essex” and then do a join on the location_name field of the locations table.

Here is my current query:

SELECT locations . *
FROM locations
GROUP BY locations.id
ORDER BY locations.location_name
LIMIT 0 , 30
2 
TRIM(SUBSTRING_INDEX(display_name, ',' , -1))

by the way, ditch the GROUP BY

:slight_smile:

3

The correct answer is: normalize your database :slight_smile:

Otherwise you could try with locate and [URL=“http://dev.mysql.com/doc/refman/5.5/en/string-functions.html#function_substring”]substring

Ok, forget about locate, Rudy already gave you the answer. I’d still say you need to split that column in two pieces though.

4

Thanks Rudy!

I now have the following SQL, but it doesn’t return the correct count. What I am trying to achieve is a count on all rows returned from locations even if none are matched against job_locations table. If that makes any sense :slight_smile:

SELECT locations . * , count( jobs_locations.job_id ) AS count
FROM locations
INNER JOIN jobs_locations ON TRIM( SUBSTRING_INDEX( jobs_locations.display_name, ',', -1 ) ) = locations.location_name
ORDER BY locations.location_name
LIMIT 0 , 30
5

Yes it does. You need a LEFT JOIN for that.

6

so would i :slight_smile: