changing column values in bigquery

问题:

I have a table where there is a column with different values like America, South Korea, Japan and so on. I would like to replace the values with America=USA, South Korea=SA, Japan= JP like these. What would be the code?

回答1:

The best way to probably handle this would be to maintain a separate table which maps full country names to their two letter codes:

country_full | country_abbr
America      | USA
South Korea  | SA
Japan        | JP

Then, you may join your current table to this lookup table to bring in the codes:

SELECT
    t1.*,
    t2.country_abbr
FROM yourTable t1
LEFT JOIN country_codes t2
    ON t1.country = t2.country_full;

Another way to handle this, though not very scalable, would be to use a CASE expression to bring in the codes:

SELECT
    country,
    CASE country WHEN 'America'     THEN 'USA'
                 WHEN 'South Korea' THEN 'SA'
                 WHEN 'Japan'       THEN 'JP'
                 ELSE 'Unknown' END As code
FROM yourTable;