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

MySQL String: Exercise-10 with Solution

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

location_id  street_address        postal_code  city        state_province  country_id
-----------  --------------------  -----------  ----------  --------------  ----------
1000         1297 Via Cola di Rie  989          Roma                        IT
1100         93091 Calle della Te  10934        Venice                      IT
1200         2017 Shinjuku-ku      1689         Tokyo       Tokyo Prefectu  JP
1300         9450 Kamiya-cho       6823         Hiroshima                   JP
1400         2014 Jabberwocky Rd   26192        Southlake   Texas           US
1500         2011 Interiors Blvd   99236        South San   California      US
1600         2007 Zagora St        50090        South Brun  New Jersey      US
1700         2004 Charade Rd       98199        Seattle     Washington      US
1800         147 Spadina Ave       M5V 2L7      Toronto     Ontario         CA
1900         6092 Boxwood St       YSW 9T2      Whitehorse  Yukon           CA
2000         40-5-12 Laogianggen   190518       Beijing                     CN
2100         1298 Vileparle (E)    490231       Bombay      Maharashtra     IN
2200         12-98 Victoria Stree  2901         Sydney      New South Wale  AU
2300         198 Clementi North    540198       Singapore                   SG
2400         8204 Arthur St                     London                      UK
2500         Magdalen Centre, The  OX9 9ZB      Oxford      Oxford          UK
2600         9702 Chester Road     9629850293   Stretford   Manchester      UK
2700         Schwanthalerstr. 703  80925        Munich      Bavaria         DE
2800         Rua Frei Caneca 1360  01307-002    Sao Paulo   Sao Paulo       BR
2900         20 Rue des Corps-Sai  1730         Geneva      Geneve          CH
3000         Murtenstrasse 921     3095         Bern        BE              CH
3100         Pieter Breughelstraa  3029SK       Utrecht     Utrecht         NL
3200         Mariano Escobedo 999  11932        Mexico Cit  Distrito Feder  MX

Code:

-- This SQL query selects the location ID, street address, and extracts the last word from the street address.

SELECT 
location_id, -- Selecting the location ID from the locations table.
street_address, -- Selecting the street address from the locations table.

    -- Extracting the last word from the street address by replacing punctuation marks with spaces,
    -- splitting the address into words, and then selecting the last word.
    SUBSTRING_INDEX(
REPLACE(
REPLACE(
REPLACE(street_address,',',' '),
            ')',' '),
        '(',' '),
' ',-1) AS 'Last--word-of-street_address' 

FROM 
locations; -- Specifies the table from which data is being retrieved, in this case, it's the 'locations' table.

Explanation:

• The SELECT statement retrieves data from the specified table (locations).
• location_id and street_address columns are selected directly.
• The nested REPLACE functions are used to replace commas, closing parentheses, and opening parentheses with spaces in the street_address.
• SUBSTRING_INDEX() function is then used to extract the last word from the modified street_address.
• The result of this operation is aliased as 'Last--word-of-street_address' in the output.

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 MySQL query to extract the last 4 character of phone numbers.
Next:Write a MySQL query to get the locations that have minimum street length.