Regular expression in Oracle DB

Question

I'm trying to use REGEXP_LIKE in my table to check if the score of the game has the pattern:

(1 or 2 numbers)x(1 or 2 numbers)

My attempt was to use this expression

CONSTRAINT CK_PLACAR CHECK (REGEXP_LIKE (PLACAR, '^[[:digit:]]+x[[:digit:]]+$', 'i'))

But I'm not able to insert a score like '1x0'. I also tried some other options, like:

CONSTRAINT CK_PLACAR CHECK (REGEXP_LIKE (PLACAR, '^[[:digit:]]{1,2}x[[:digit:]]{1,2}$', 'i'));
CONSTRAINT CK_PLACAR CHECK (REGEXP_LIKE (PLACAR, '^[[:digit:]]*[[:digit:]]x[[:digit:]][[:digit:]]*$', 'i'));

I tried to change [[:digit:]] to [0-9] as well, but it didn't work either.

Here is my complete table:

CREATE TABLE PARTIDA (
TIME1 VARCHAR2(50) NOT NULL,
TIME2 VARCHAR2(50) NOT NULL,
DATA DATE NOT NULL,
PLACAR CHAR(5) DEFAULT '0x0',
LOCAL VARCHAR2(50) NOT NULL,
CONSTRAINT PK_PARTIDA PRIMARY KEY (TIME1, TIME2, DATA),
CONSTRAINT FK_PARTIDA FOREIGN KEY (TIME1, TIME2) REFERENCES JOGA(TIME1, TIME2),
CONSTRAINT CK_PLACAR CHECK (REGEXP_LIKE (PLACAR, '^[[:digit:]]+x[[:digit:]]+$', 'i'))
);

Here is my test case:

INSERT INTO PARTIDA VALUES ('TIME1', 'TIME2', SYSDATE, '1x0', 'ESTADIO1');

Here is the output:

Error starting at line : 1 in command - INSERT INTO PARTIDA VALUES ('TIME1', 'TIME2', SYSDATE, '1x0', 'ESTADIO1') Error report - ORA-02290: check constraint (K9012931.CK_PLACAR) violated

The plus sign after the digit means 1 or more but you only allocated size for 2 on each side of the 'x'. Either increase the size of the column or restrict what the regex allows. — Gary_W
– Gary_W, Commented Nov 1, 2018 at 15:01
Adding this comment here also so you see it. Make column PLACAR a VARCHAR2 instead of a CHAR and it should work. Don't ask me why. — Gary_W
– Gary_W, Commented Nov 1, 2018 at 16:48
@Gary_W It's because CHAR are fixed-width - if you don't use the whole 5 bytes in this case, your data gets right-padded with spaces, which makes it fail the regexp. — kfinity
– kfinity, Commented Nov 1, 2018 at 18:05
@kfinity Thanks for the info. If I make the regex '^\d{1,2}x\d{1,2} *$' to allow for the padding of 0 or more spaces like you said it works. Good to know but I'd stick with using VARCHAR2 anyway. — Gary_W
– Gary_W, Commented Nov 1, 2018 at 18:53

Gary_W · Accepted Answer · 2018-11-01 18:59:30Z

1

Try '^\d{1,2}x\d{1,2}$' for at least 1 but not more than 2 digits on either side of the 'x'.

Maybe it's the syntax? Try this:

  ALTER TABLE score_table ADD (
    CONSTRAINT CK_PLACAR
    CHECK (REGEXP_LIKE (PLACAR, '^\d{1,2}x\d{1,2}$', 'i'))
    ENABLE VALIDATE);

EDIT thanks to kfinity's comment above. With PLACAR having a datatype of CHAR(5), that's a fixed-width datatype so if the data entered is less than 5 characters it gets padded with spaces causing it to not match the regex pattern. Either change the datatype to VARCHAR2(5) which is variable width and preferred, or change the regex to allow for possible zero or more spaces at the end:

'^\d{1,2}x\d{1,2} *$'

edited Nov 1, 2018 at 18:59

answered Nov 1, 2018 at 13:38

Gary_W

10.4k1 gold badge26 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Kollins Lima Over a year ago

I'm still getting the same error. I edited my question, adding the complete table and the test case.

Gary_W · Accepted Answer · 2018-11-01 13:56:09Z

0

Just like Gary W's answer, you can also modify it to not accept scores like '0x' (01, 02, 03, ...) but accept 0 or simply 1, 2, 3 etc.

^(?!0\d)\d{1,2}x(?!0\d)\d{1,2}$

edited Nov 1, 2018 at 13:56

Gary_W

10.4k1 gold badge26 silver badges42 bronze badges

answered Nov 1, 2018 at 13:49

Tiberiu Zulean

1723 silver badges13 bronze badges

1 Comment

Gary_W Over a year ago

Sorry I edited your answer instead of my own! duh P.S. Oracle does not support negative lookaheads though.

Collectives™ on Stack Overflow

Regular expression in Oracle DB

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related