Search code examples
oracleconstraintscheck-constraints

Oracle - Check constraint maximum number of x words

I want to add a check constraint that checks whether a field has a maximum number of X white spaces " ". I couldn't find anything about it on the oracle website. Anybody knows whether that's possible? Maybe through a PL/SQL function?

Solution

  • If you're defining a word by counting the number of spaces, then you could probably do something like this:

    constraint check_ws_count check (length(regexp_replace(field,'[^ ]','')) <= 99)

    However, this doesn't take into account double spacing, etc. Maybe you can tweak it to be more robust, but it doesn't seem like a good idea!

    EDIT Using regexp_count and taking multiple spacing into account:

    constraint check_ws_count check (regexp_count(field, '\s+') <= 99)