CODEWITHSUNDEEP
sqlregexoracle-database
Kollins Lima
Kollins Lima

Reputation: 13

Regular expression in Oracle DB

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

Upvotes: 1

Views: 107

Answers (2)

Gary_W
Gary_W

Reputation: 10360

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} *$'

Upvotes: 1

Tiberiu Zulean
Tiberiu Zulean

Reputation: 172

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}$

Upvotes: 0

Related Questions