MySQL String Exercises: Get the last word of the street address

MySQL String: Exercise-10 with Solution

Write a query to get the last word of the street address.

Sample table: locations

Code:

SELECT location_id, street_address, 
SUBSTRING_INDEX(REPLACE(REPLACE(REPLACE(street_address,',',' '),')',' '),'(',' '),' ',-1) 
AS 'Last--word-of-street_address' 
FROM locations;

Sample Output:

location_id	street_address			Last--word-of-street_address
1000		1297 Via Cola di Rie		Rie
1100		93091 Calle della Testa		Testa
1200		2017 Shinjuku-ku		Shinjuku-ku
1300		9450 Kamiya-cho			Kamiya-cho
1400		2014 Jabberwocky Rd		Rd
1500		2011 Interiors Blvd		Blvd
1600		2007 Zagora St			St
1700		2004 Charade Rd			Rd
1800		147 Spadina Ave			Ave
1900		6092 Boxwood St			St
2000		40-5-12 Laogianggen		Laogianggen
2100		1298 Vileparle (E)	
2200		12-98 Victoria Street		Street
2300		198 Clementi North		North
2400		8204 Arthur St			St
2500		"Magdalen Centre		Centre
2600		9702 Chester Road		Road
2700		Schwanthalerstr. 7031		7031
2800		Rua Frei Caneca 1360		1360
2900		20 Rue des Corps-Saints		Corps-Saints
3000		Murtenstrasse 921		921
3100		Pieter Breughelstraat 837	837
3200		Mariano Escobedo 9991		9991

MySQL Code Editor:

Have another way to solve this solution? Contribute your code (and comments) through Disqus.

Previous:Write a query to extract the last 4 character of phone numbers.
Next:Write a query to get the locations that have minimum street length.