CODEWITHSUNDEEP
sqloracle-database
SevO
SevO

Reputation: 303

Check exact digit in number in SQL

When I insert a number into a table, I need to check, if some digits of this number fulfill few constraints. Is there a way I can accomplish this?
In Python, for example, I could use index number[n] or regex. I thought of using LIKE, but i had read that LIKE is only used in WHERE clause so it won't work.

Simple code:

CREATE TABLE Person(  
    id INT NOT NULL, 
    name VARCHAR(40)  
)    

-- this constraint is just how I would imagine it 
ALTER TABLE Person ADD CONSTRAINT Check_id CHECK (id[2] <> 0 AND id[3] <> 0);  

INSERT INTO Person(1234, 'John'); -- not error
INSERT INTO Person(1204, 'Mike'); -- error

Thank you for any advice.

Upvotes: 0

Views: 1087

Answers (3)

user5683823
user5683823

Reputation:

In most cases it is best to use standard string functions and operators (INSTR, SUBSTR, LIKE etc.) over regular expression solutions, which are much more time- and resource-consuming. Use regular expressions only for complicated problems, where the standard functions either simply don't work or give a solution that is difficult to write and to maintain.

In this case, a simple solution is

ALTER TABLE Person 
      ADD CONSTRAINT Check_id CHECK ( 0 NOT IN (SUBSTR(id, 2, 1), SUBSTR(id, 3, 1)) );

Upvotes: 0

dnoeth
dnoeth

Reputation: 60462

You can base your check on a Regular Expression, e.g. to check that the last two digits are not zero:

check (regexp_like(to_char(id), '[^0][^0]$'))

[^0] = not a zero

$ = end of string

Or to check that the 2nd and 3rd digit is not a zero:

check (regexp_like(to_char(id), '^.[^0][^0]))

.= any character

^ = begin of string/number

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269543

You can do:

ALTER TABLE Person ADD CONSTRAINT chk_person_id
    CHECK (id not like '_0%' and id not like '__0%');

Oracle will convert the id to a string for the like. I'm not really thrilled with representing the id as a number. Personally, I think it would be better to represent it as a string.

Upvotes: 0

Related Questions