Replace string from another table-CodePudding

Suppose I have addresses and country_codes tables.
addresses table has incorrect country_code values and I want to replace them with country_code from the country_codes table based on the address.
How to do that in Snowflake?

Note that the address column might have a country name or country code in it.

Tables:

 -------------- ----------------------------------- 
| country_code |              address              |
 -------------- ----------------------------------- 
| US           | 1145 Oakmound Drive, US           |
| W            | 4733 Pallet Street, United States |
| F            | Rua Wanda Carnio 190, Brazil      |
| 22           | Via delle Viole 137, Italy        |
| 50           | 7 Essex Rd, GB                    |
 -------------- ----------------------------------- 

 -------------- ---------------- 
| country_code |    country     |
 -------------- ---------------- 
| GB           | United Kingdom |
| BR           | Brazil         |
| IT           | Italy          |
| US           | United States  |
 -------------- ----------------

Desired output:

 -------------- ----------------------------------- 
| country_code |              address              |
 -------------- ----------------------------------- 
| US           | 1145 Oakmound Drive, US           |
| US           | 4733 Pallet Street, United States |
| BR           | Rua Wanda Carnio 190, Brazil      |
| IT           | Via delle Viole 137, Italy        |
| GB           | 7 Essex Rd, GB                    |
 -------------- -----------------------------------

CodePudding user response：

Something like this can give you the expected result:

select c.country_code, a.address from addresses a join country_codes c
ON c.country_code = a.country_code OR ( POSITION ( c.country_code, a.address ) OR POSITION ( c.country, a.address )); 

 -------------- ----------------------------------- 
| COUNTRY_CODE |              ADDRESS              |
 -------------- ----------------------------------- 
| US           | 1145 Oakmound Drive, US           |
| US           | 4733 Pallet Street, United States |
| BR           | Rua Wanda Carnio 190, Brazil      |
| IT           | Via delle Viole 137, Italy        |
| GB           | 7 Essex Rd, GB                    |
 -------------- -----------------------------------

CodePudding user response：

You could join on the condition that the last part of address includes either the country or country_code.

select c.country_code, a.address 
from addresses a 
join country_codes c on trim(split_part(a.address,',',-1)) ilike any (c.country_code, c.country)