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

没错，Postgres不支持UTF-16。

但是，它确实支持Unicode转义序列。

SELECT U&'\0633\0644\0627\0645'

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

Answer 2

PostgreSQL本机不支持UTF-16 .我建议您将数据转换为UTF-8，然后再提供给DB。如果为时已晚(数据库中已经存在错误的数据)，您可以使用这些维护功能从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 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 
--------------
 سلام