PostgreSQL中的国际化正则表达式 [英] internationalized regular expression in postgresql

问题描述

如何在postgres中编写正则表达式以匹配诸如José之类的名称。换句话说，我需要设置一个约束以检查仅输入了有效名称，但还希望允许使用Unicode字符。

How can write regular expressions to match names like 'José' in postgres.. In other words I need to setup a constraint to check that only valid names are entered, but want to allow unicode characters also.

正则表达式， Unicode样式对此有一些参考。但是，看来我无法在postgres中编写它。

Regular expressions, unicode style have some reference on this. But, it seems I can't write it in postgres.

如果无法为此编写正则表达式，仅在客户端进行检查就足够了使用javascript

If it is not possible to write a regex for this, will it be sufficient to check only on client side using javascript

推荐答案

PostgreSQL不像.NET那样支持基于Unicode字符数据库的字符类。您将获得更标准的 [[：alpha：]] 字符类，但这是与语言环境相关的，可能不会覆盖它。

PostgreSQL doesn't support character classes based on the Unicode Character Database like .NET does. You get the more-standard [[:alpha:]] character class, but this is locale-dependent and probably won't cover it.

您只需将不需要的ASCII字符列入黑名单，并允许所有非ASCII字符就可以逃脱。例如类似

You may be able to get away with just blacklisting the ASCII characters you don't want, and allowing all non-ASCII characters. eg something like

[^\s!"#$%&'()*+,\-./:;<=>?\[\\\]^_`~]+

（JavaScript也没有非ASCII字符类。甚至没有 [[:::]] 。）

(JavaScript doesn't have non-ASCII character classes either. Or even [[:alpha:]].)

例如，给定 v_text 作为要清除的文本变量：

For example, given v_text as a text variable to be sanitzed:

-- Allow internationalized text characters and remove undesired characters
v_text = regexp_replace( lower(trim(v_text)), '[!"#$%&()*+,./:;<=>?\[\\\]\^_\|~]+', '', 'g' );

这篇关于PostgreSQL中的国际化正则表达式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！