Group By a substring in a field

Question

I have a cities table in which I have citynames like "Los Angeles (California)".

I tried to extract all the values contained between parantheses with a request, but I couldn't manage to do it well...

The request I tried looked like this :

SELECT cityname FROM cities WHERE cityname LIKE "%(%)%" 
GROUP BY SUBSTR(cityname, FIND_IN_SET(cityname, '('))

Or something like (without group by):

SELECT SUBSTR(cityname, FIND_IN_SET(cityname, '(')) FROM cities 
WHERE cityname LIKE "%(%)%"

What seems to be wrong with these requests?

Answer 1

Try this :

SELECT SUBSTRING(cityname, CHARINDEX('(',cityname)+1,
           (LEN(cityname) - CHARINDEX('(',cityname)-1)) 
FROM cities WHERE cityname LIKE '(%)%'