Postgresql convert Japanese Full-Width to Half-Width

Question

I am manipulating Japanese data and in some Japanese words, there are English words and Numbers are in.

ＳＹＳＫＥＮ, 松井ケ丘３, コメリＨ＆Ｇ, 篠路７－１ are the examples.

I wanted to convert these English and Numbers in Full-width to half-width by throwing a function or any possible ways.

the output of the input above should be look-like SYSKEN, 松井ケ丘3, コメリH&G, 篠路7-1

If anyone knows the best way to start, I would appreciate it.

Answer 1

How about using translate() function?

-- prepare test data
CREATE TABLE address (
    id integer,
    name text
);
INSERT INTO address VALUES (1, 'ＳＹＳＫＥＮ, 松井ケ丘３, コメリＨ＆Ｇ, 篠路７－１');

-- show test data
SELECT * from address;

-- convert Full-Width to Half-Width Japanese
UPDATE address SET name = translate(name,
    '０１２３４５６７８９ＡＢＣＤＥＦＧＨＩＪＫＬＭＮＯＰＱＲＳＴＵＶＷＸＹＺ',
    '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ'
);

-- see the converted data
SELECT * from address;

This code made the name column to "SYSKEN, 松井ケ丘3, コメリH＆G, 篠路7－1".