International regex in postgresql

Question

How to write regular expressions for matching names such as "José" in postgres. In other words, I need to set a restriction to verify that only valid names are entered, but I also need to specify Unicode characters.

Regular expressions, Unicode style have some links to this. But it seems I can not write this in postgres.

If for this it is impossible to write a regular expression, it will be enough to check only on the client side using javascript

Answer 1

PostgreSQL does not support Unicode character database character classes, such as .NET. You get a more standard character class [[:alpha:]] , but it depends on the language and probably won't cover it.

You might just be able to mask ASCII characters that you don't want and allow all non-ASCII characters. for example, something like

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

(JavaScript does not have character classes other than ASCII, or even [[:alpha:]] .)

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

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