internationalized regular expression in postgresql

Question

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.

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

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

Answer 1

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.

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 doesn't have non-ASCII character classes either. Or even [[:alpha:]].)

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' );