logo标签列表

在PostgreSQL中删除所有Unicode空格分隔符？

regexpostgresqlunicodetrimremoving-whitespace
4
我想要使用trim()函数来剪裁一列，并将任何多个空格和Unicode空格分隔符替换为单个空格。这样做的目的是为了清理用户名，防止出现两个用户拥有欺骗性的名称foo bar(空格u+20)与foo bar（不间断空格u+A0）。
到目前为止，我已经使用了SELECT regexp_replace(TRIM('some string'), '[\s\v]+', ' ', 'g');语句，它可以去除空格、制表符和回车，但它缺少对Unicode空格分隔符的支持。
我本想在正则表达式中添加\h，但PostgreSQL不支持它（也不支持\p{Zs}）。
SELECT regexp_replace(TRIM('some string'), '[\s\v\h]+', ' ', 'g');


Error in query (7): ERROR: invalid regular expression: invalid escape \ sequence

我们正在Debian 10的Docker容器中运行 PostgreSQL 12 (12.2-2.pgdg100+1)，使用UTF-8编码，并支持在用户名中使用表情符号。是否有一种类似的方法可以实现？
1
您是否想允许使用3个或更多字节编码的任何字符？\u0020使用2个字节进行编码，\u00A0使用3个字节进行编码，链接中列出的其余字符使用4个字节进行编码。除非您有中文字符（或类似字符），否则您可能希望修剪使用4个字节编码的所有字符.... - Erwin Brandstetter
1
密切相关：https://dev59.com/22Eh5IYBdhLWcg3wMA7M#22701212 - Erwin Brandstetter
3个回答
4

根据Posix“space”字符类（Postgres正则表达式中的类别简写\s），UNICODE“空格”，一些类似“格式字符”的空格以及一些额外的非可打印字符（从Wiktor的帖子最后添加了两个），我压缩了这个自定义字符类：

'[\s\u00a0\u180e\u2007\u200b-\u200f\u202f\u2060\ufeff]'

所以使用:

SELECT trim(regexp_replace('some string', '[\s\u00a0\u180e\u2007\u200b-\u200f\u202f\u2060\ufeff]+', ' ', 'g'));

注意：trim() 在regexp_replace()之后执行，因此它处理已转换的空格。
重要的是要包括基本空格类\s（缩写为[[:space:]]），以涵盖所有当前（和未来）的基本空格字符。
我们可能会包含更多字符。或者先开始剥离所有使用4字节编码的字符。因为 UNICODE 黑暗而充满恐惧。
考虑这个演示：
SELECT d AS decimal, to_hex(d) AS hex, chr(d) AS glyph
     , '\u' || lpad(to_hex(d), 4, '0') AS unicode
     , chr(d) ~ '\s' AS in_posix_space_class
     , chr(d) ~ '[\s\u00a0\u180e\u2007\u200b-\u200f\u202f\u2060\ufeff]' AS in_custom_class
FROM  (
   -- TAB, SPACE, NO-BREAK SPACE, OGHAM SPACE MARK, MONGOLIAN VOWEL, NARROW NO-BREAK SPACE
   -- MEDIUM MATHEMATICAL SPACE, WORD JOINER, IDEOGRAPHIC SPACE, ZERO WIDTH NON-BREAKING SPACE
   SELECT unnest('{9,32,160,5760,6158,8239,8287,8288,12288,65279}'::int[])
   UNION ALL
   SELECT generate_series (8192, 8202) AS dec  -- UNICODE "Spaces"
   UNION ALL
   SELECT generate_series (8203, 8207) AS dec  -- First 5 space-like UNICODE "Format characters"
   ) t(d)
ORDER  BY d;


 decimal | hex  |  glyph   | unicode | in_posix_space_class | in_custom_class 
---------+------+----------+---------+----------------------+-----------------
       9 | 9    |          | \u0009  | t                    | t
      32 | 20   |          | \u0020  | t                    | t
     160 | a0   |          | \u00a0  | f                    | t
    5760 | 1680 |          | \u1680  | t                    | t
    6158 | 180e | ᠎        | \u180e  | f                    | t
    8192 | 2000 |          | \u2000  | t                    | t
    8193 | 2001 |          | \u2001  | t                    | t
    8194 | 2002 |          | \u2002  | t                    | t
    8195 | 2003 |          | \u2003  | t                    | t
    8196 | 2004 |          | \u2004  | t                    | t
    8197 | 2005 |          | \u2005  | t                    | t
    8198 | 2006 |          | \u2006  | t                    | t
    8199 | 2007 |          | \u2007  | f                    | t
    8200 | 2008 |          | \u2008  | t                    | t
    8201 | 2009 |          | \u2009  | t                    | t
    8202 | 200a |          | \u200a  | t                    | t
    8203 | 200b | ​        | \u200b  | f                    | t
    8204 | 200c | ‌        | \u200c  | f                    | t
    8205 | 200d | ‍        | \u200d  | f                    | t
    8206 | 200e | ‎        | \u200e  | f                    | t
    8207 | 200f | ‏        | \u200f  | f                    | t
    8239 | 202f |          | \u202f  | f                    | t
    8287 | 205f |          | \u205f  | t                    | t
    8288 | 2060 | ⁠        | \u2060  | f                    | t
   12288 | 3000 | 　       | \u3000  | t                    | t
   65279 | feff |         | \ufeff  | f                    | t
(26 rows)

用于生成字符类的工具：

SELECT '[\s' || string_agg('\u' || lpad(to_hex(d), 4, '0'), '' ORDER BY d) || ']'
FROM  (
   SELECT unnest('{9,32,160,5760,6158,8239,8287,8288,12288,65279}'::int[])
   UNION ALL
   SELECT generate_series (8192, 8202)
   UNION ALL
   SELECT generate_series (8203, 8207)
   ) t(d)
WHERE  chr(d) !~ '\s'; -- not covered by \s


[\s\u00a0\u180e\u2007\u200b\u200c\u200d\u200e\u200f\u202f\u2060\ufeff]

在此处访问db<>fiddle

相关链接，更多解释：

在 regexp_replace() 后面加上 trim() 是一个非常有用的技巧！谢谢。 - KumZ
2

您可以构建一个括号表达式，其中包含来自\p{Zs} Unicode类别和一个制表符的空格字符：

REGEXP_REPLACE(col, '[\u0009\u0020\u00A0\u1680\u2000-\u200A\u202F\u205F\u3000]+', ' ', 'g')

它将使用一个普通的空格字符替换所有水平空白符的一个或多个出现（在其他支持\h的正则表达式中匹配）。

1
看起来起作用了，谢谢。我已经尝试过类似的东西，但只使用\u00A0而不是写成\uA0，这是我的错误！ ;) - KumZ
1
@KumZ 你可以在PostgreSQL正则表达式文档中查看所有支持的转义序列（也称为“字符输入转义”）。 - Wiktor Stribiżew
0
编译来自多个来源的空白字符，我最终得到了以下模式，其中包括制表符（U+0009 / U+000B / U+0088-008A / U+2409-240A），单词连接器（U+2060），空格符号（U+2420 / U+2423），盲文空白（U+2800），标签空格（U+E0020）等等。
[\x0009\x000B\x0088-\x008A\x00A0\x1680\x180E\x2000-\x200F\x202F\x205F\x2060\x2409\x240A\x2420\x2423\x2800\x3000\xFEFF\xE0020]

为了有效地转换包括多个连续空格以及位于列开头/结尾的空白，以下是需要按顺序执行的3个查询（假设是从“mytable”中的“text”列）的内容：
-- transform all Unicode blanks/spaces into a "regular" one (U+20) only on lines where "text" matches the pattern
UPDATE
    mytable
SET
    text = regexp_replace(text, '[\x0009\x000B\x0088-\x008A\x00A0\x1680\x180E\x2000-\x200F\x202F\x205F\x2060\x2409\x240A\x2420\x2423\x2800\x3000\xFEFF\xE0020]', ' ', 'g')
WHERE
    text ~ '[\x0009\x000B\x0088-\x008A\x00A0\x1680\x180E\x2000-\x200F\x202F\x205F\x2060\x2409\x240A\x2420\x2423\x2800\x3000\xFEFF\xE0020]';

-- then squeeze multiple spaces into one
UPDATE mytable SET text=regexp_replace(text, '[ ]+ ',' ','g') WHERE text LIKE '%  %';

-- and finally, trim leading/ending spaces
UPDATE mytable SET text=trim(both ' ' FROM text) WHERE text LIKE ' %' OR text LIKE '% ';
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接