Postgres regex to restrict forbidden characters and limit number of consequtive caps

user_1772830 0
Viewed 119

I want to add few identical constraints to 2 tables (on multiple columns).

To this end I want to create functions to be used in check constraint.

The validation should fail if the value contains any of the following (the string will always contain at least some letters):

  • .. (two consequent dots)
  • >
  • <
  • more, than 3 consecutive capital letters
  • all letters are capital (must contain at least 1 lowercased letter)

So far I have the following:

CREATE FUNCTION no_forbidden_characters(text) RETURNS boolean
  LANGUAGE sql IMMUTABLE STRICT
AS $_$
  SELECT $1 !~ '[a-z]+|..|>|<';
$_$;

Not sure how to get the max 3 capital letters in a row (though allowing for multiple separate by a space 3 capital letter combinations)

postgresql regex
1 Answers

I believe you can use

(^|[^.])[.]{2}([^.]|$)|[<>]|[[:upper:]]{4}|^[[:upper:]]+$

It matches (and thus, since you are using the negated regex matching operator, rejects) the following patterns:

  • (^|[^.])[.]{2}([^.]|$) - two dots that is preceded with start of string or a non-dot char and followed with a non-dot char or end of string (so, only .. string, no ...)
  • [<>] - a < or >
  • [[:upper:]]{4} - four consecutive uppercase letters
  • ^[[:upper:]]+$ - a string fully composed of uppercase letters.

See the regex demo.

user_38329700
Related