Oracle - Check constraint maximum number of x words

Question

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?

Can you give us at least one example? (I'm not quite sure what you mean!) — Colin 't Hart, Oct 26 '12 at 15:27
What defines a word? Can you simply look for the number of space characters? Do you need to use other delimiters? If so, what delimits a word? — Justin Cave, Oct 26 '12 at 15:27
@Colin 't Hart Sorry about that, I know what you mean. I basically want a maximum number of 99 white spaces " " — bicycle, Oct 26 '12 at 15:37
@Justin Cave Sorry about that, I know what you mean. I basically want a maximum number of 99 white spaces " " — bicycle, Oct 26 '12 at 15:38
Do you mean total white space in the field or just leading/trailing? giving an example of what you are putting into the field will help people answer your question. — Joe W, Oct 26 '12 at 15:44
@Joe W I basically want that one field to have a maximum number of 100 words. Since text strings don't start and end with a white space " ", 99 white spaces should therefore be all right — bicycle, Oct 26 '12 at 15:48

Xophmeister · Accepted Answer · 2012-10-26T15:55:41.983

5

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)

edited Oct 26 '12 at 15:55

answered Oct 26 '12 at 15:44

Xophmeister

8,884
4
44
87

2

Using `regexp_count` would be simpler. – Nick Krasnov Oct 26 '12 at 15:45
1

@NicholasKrasnov I did not know about `regexp_count`: +1 to you :) – Xophmeister Oct 26 '12 at 15:46
...Ah, it was introduced in 11g and I'm still stuck with 10g. – Xophmeister Oct 26 '12 at 15:49
Is regexp_count an aggregate function?! – Colin 't Hart Oct 26 '12 at 15:50
@Colin'tHart No: http://docs.oracle.com/cd/B28359_01/server.111/b28286/functions135.htm – Xophmeister Oct 26 '12 at 15:50
Sorry -- that was me shooting off the hip. Pity that the semantics of the function names has been broken. – Colin 't Hart Oct 26 '12 at 15:51
@Xophmeister I have 11g! Thanks for your tip. I'll take a look how to do it in regexp_count – bicycle Oct 26 '12 at 15:51

Colin 't Hart · Answer 2 · 2012-10-26T16:01:50.947

2

Probably something like this should do the trick:

check (length(field) - length(replace(field, ' ', '')) <= 99)

This approach will also work for those stuck on Oracle 9i or older databases without the regexp_ family of functions.

edited Oct 26 '12 at 16:01

answered Oct 26 '12 at 15:43

Colin 't Hart

7,372
3
28
51

Oracle - Check constraint maximum number of x words

2 Answers2