SQL to fix importing Latin-1 text as if it were UTF-8

encoding_fixup.sql

-- Sometimes one accidentally loads data that is in ISO8859-1 (aka "Latin-1") encoding having assumed that it was actually UTF-8
-- so far it seems like à is a good flag although if your data might also have that correctly, this is less simple...
update tiger2020.census_name_lookup 
    set simple_name =       replace(simple_name,       'ñ', 'ñ' ), 
        display_name =      replace(display_name,      'ñ', 'ñ' ), 
        prefix_match_name = replace(prefix_match_name, 'ñ', 'ñ' );
update tiger2020.census_name_lookup 
    set simple_name =       replace(simple_name,       'ü', 'ü' ), 
        display_name =      replace(display_name,      'ü', 'ü' ),  
        prefix_match_name = replace(prefix_match_name, 'ü', 'ü' );
update tiger2020.census_name_lookup
    set simple_name =       replace(simple_name,       'ó', 'ó' ), 
        display_name =      replace(display_name,      'ó', 'ó' ), 
        prefix_match_name = replace(prefix_match_name, 'ó', 'ó' );
update tiger2020.census_name_lookup 
    set simple_name =       replace(simple_name,       'á', 'á' ),  
        display_name =      replace(display_name,      'á', 'á' ),  
        prefix_match_name = replace(prefix_match_name, 'á', 'á' );
update tiger2020.census_name_lookup 
    set simple_name =       replace(simple_name,       'é', 'é' ), 
        display_name =      replace(display_name,      'é', 'é' ), 
        prefix_match_name = replace(prefix_match_name, 'é', 'é' );
update tiger2020.census_name_lookup 
    set simple_name =       replace(simple_name,       'í', 'í' ), -- note invisible second character 
        display_name =      replace(display_name,      'í', 'í' ), -- '\xad' in string to be replaced...
        prefix_match_name = replace(prefix_match_name, 'í', 'í' );
update tiger2020.census_name_lookup 
    set simple_name =       replace(simple_name,       'Ã¥', 'å' ), -- This only appears in Hagåtña CDP, Guam which isn't in CR
        display_name =      replace(display_name,      'Ã¥', 'å' ), -- but it may be useful in other cases.
        prefix_match_name = replace(prefix_match_name, 'Ã¥', 'å' );