Group by geo with delimiters

code Egypt_Africa
(2) Seoul_Korea_Asia
(3) China_Asia
(4) Japan_Asia
(5) Berlin_Germany_Europe
(6) Oxford Street_London_England_Europe
(7) Pusan_Korea_Asia
(8) Peking_China_Asia
(9) tiananmen_Peking_China_Asia[/code]The above is the result of the query below.

select id, geo from geo4 order by id
I like to produce group by country which is delimted by 2nd delimiter from the last.

The following code doesn’t work correctly but it will show what I want.

geo(2nd delimiter '_' from the last) from geo3 group by geo(2nd delimiter '_' from the last) The below is my target result.

Egypt_Africa Korea_Asia China_Asia Japan_Asia Germany_Europe England_Europe

joon, allow me to introduce you to mysql’s SUBSTRING_INDEX function

it’s fully documented in the manual

hint: you need a negative index to start at the right

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.