UTF16十六进制到文本

Question

我有UTF-16十六进制表示，如"0633064406270645"，在阿拉伯语中是"الام"。

我想把它转换成等效的文本。在PostgreSQL中有直接的方法吗？

我可以像下面这样转换UTF代码点；不幸的是，似乎不支持UTF16。有没有关于如何在PostgreSQL中做到这一点的想法，最坏的情况下我会写一个函数？

SELECT convert_from (decode (E'D8B3D984D8A7D985', 'hex'),'UTF8');
"سلام"
SELECT convert_from (decode (E'0633064406270645', 'hex'),'UTF16');
ERROR:  invalid source encoding name "UTF16"
********** Error **********

Answer 1

PostgreSQL本机不支持UTF-16。我建议您在将数据提供给数据库之前将其转换为UTF-8。如果为时已晚（数据库中已经存在错误的数据），您可以使用这些维护功能转换UTF-16中的数据（从维基百科复制的逻辑）：

-- convert from bytea, containing UTF-16-BE data
CREATE OR REPLACE FUNCTION convert_from_utf16be(utf16_data bytea, invalid_replacement text DEFAULT '?')
  RETURNS text
  LANGUAGE sql
  IMMUTABLE
  STRICT
AS $function$
WITH source(unit) AS (
  SELECT (get_byte(utf16_data, i) << 8) | get_byte(utf16_data, i + 1)
  FROM generate_series(0, octet_length(utf16_data) - 2, 2) i
),
codes(lag, unit, lead) AS (
  SELECT lag(unit, 1) OVER (), unit, lead(unit, 1) OVER ()
  FROM source
)
SELECT string_agg(CASE
  WHEN unit >= 56320 AND unit <= 57343 THEN CASE
    WHEN lag >= 55296 AND lag <= 56319 THEN '' -- already processed
    ELSE invalid_replacement
  END
  WHEN unit >= 55296 AND unit <= 56319 THEN CASE
    WHEN lead >= 56320 AND lead <= 57343 THEN chr((unit << 10) + lead - 56613888)
    ELSE invalid_replacement
  END
  ELSE chr(unit)
END, '')
FROM codes
$function$;
-- convert from bytea, containing UTF-16-LE data
CREATE OR REPLACE FUNCTION convert_from_utf16le(utf16_data bytea, invalid_replacement text DEFAULT '?')
  RETURNS text
  LANGUAGE sql
  IMMUTABLE
  STRICT
AS $function$
WITH source(unit) AS (
  SELECT get_byte(utf16_data, i) | (get_byte(utf16_data, i + 1) << 8)
  FROM generate_series(0, octet_length(utf16_data) - 2, 2) i
),
codes(lag, unit, lead) AS (
  SELECT lag(unit, 1) OVER (), unit, lead(unit, 1) OVER ()
  FROM source
)
SELECT string_agg(CASE
  WHEN unit >= 56320 AND unit <= 57343 THEN CASE
    WHEN lag >= 55296 AND lag <= 56319 THEN '' -- already processed
    ELSE invalid_replacement
  END
  WHEN unit >= 55296 AND unit <= 56319 THEN CASE
    WHEN lead >= 56320 AND lead <= 57343 THEN chr((unit << 10) + lead - 56613888)
    ELSE invalid_replacement
  END
  ELSE chr(unit)
END, '')
FROM codes
$function$;
-- convert from bytea, containing UTF-16 data (with or without BOM)
CREATE OR REPLACE FUNCTION convert_from_utf16(utf16_data bytea, invalid_replacement text DEFAULT '?')
  RETURNS text
  LANGUAGE sql
  IMMUTABLE
  STRICT
AS $function$
SELECT CASE COALESCE(octet_length(utf16_data), 0)
  WHEN 0 THEN ''
  WHEN 1 THEN invalid_replacement
  ELSE CASE substring(utf16_data FOR 2)
    WHEN E'\xFFFE' THEN convert_from_utf16le(substring(utf16_data FROM 3), invalid_replacement)
    ELSE convert_from_utf16be(substring(utf16_data FROM 3), invalid_replacement)
  END
END
$function$;

有了这些功能，您可以从所有类型的UTF-16转换：

SELECT convert_from_utf16be(decode('0633064406270645D852DF62', 'hex')),
       convert_from_utf16le(decode('330644062706450652D862DF', 'hex')),
       convert_from_utf16(decode('FEFF0633064406270645D852DF62', 'hex')),
       convert_from_utf16(decode('FFFE330644062706450652D862DF', 'hex'));
-- convert_from_utf16be | convert_from_utf16le | convert_from_utf16 | convert_from_utf16
------------------------+----------------------+--------------------+-------------------
-- سلام𤭢                | سلام𤭢                | سلام𤭢              | سلام𤭢

Answer 2

没错，Postgres不支持UTF-16。

然而，它确实支持Unicode转义序列：

SELECT U&'633644627645'

但请记住，Unicode代码点和UTF-16代码单元仅在基本多语言平面中等效。换句话说，如果您有任何跨越多个16位代码单元的UTF-16字符，您需要自己将它们翻译到相应的代码点。

Answer 3

convert_from（或一般的PostgreSQL）不支持UTF-16，但您可以求助于其中一种支持的可选语言。

plperlu中的示例（需要数据库超级用户权限才能创建函数，如果尚未创建，则需要CREATE LANGUAGE plperlu）：

CREATE FUNCTION decode_utf16(text) RETURNS text AS $$
  require Encode;
  return Encode::decode("UTF-16BE", pack("H*", $_[0]));
$$ immutable language plperlu;
=> select decode_utf16('0633064406270645');
 decode_utf16 
--------------
 سلام