Parsing underscore delimited fields in MySQL

Question

I have the following table:

Patent      AssigneeName                        AssigneeUnparsed
-------     --------------                      ---------------
D452605     Louis Vuitton Malletier, S.A.       Paris_None_FR
D452606     Nike, Inc.                          Beaverton_OR_US
D452607     Salomon S.A.                        Metz-Tessy_None_FR

I have searched the site and cant find anything specific on how to explain parsing an underscored field into separate fields. I want to take the AssigneeAddress field and parse it into three fields (AssigneeCity, AssigneeState, AssigneeCountry)

Can someone explain how to do this or point me to a tutorial so I can attempt to figure it out?

Answer 1

select 
    substring_index('Beaverton_OR_US', '_', - 1) as AssigneeCity,
    substring_index(substring_index('Beaverton_OR_US', '_', 2),
            '_',
            - 1) as AssigneeState,
    substring_index('Beaverton_OR_US', '_', 1) as AssigneeCountry;

Update :

update tablename 
set 
    AssigneeCity = substring_index('Beaverton_OR_US', '_', - 1),
    AssigneeState = substring_index(substring_index('Beaverton_OR_US', '_', 2),
            '_',
            - 1),
    AssigneeCountry = substring_index('Beaverton_OR_US', '_', 1);