Check Constraint of String to Contain Only Digits. (Oracle SQL)

CHECK CONSTRAINT of string to contain only digits. (Oracle SQL)

REGEXP_LIKE(PROD_NUM, '^[[:digit:]]{9}$')

CHECK constraint for string to contain a certain amount of digits as well as certain digits. (Oracle SQL)

Try this.

CHECK (REGEXP_LIKE(number, '^1234[6-9]\d{6}$'))

Oracle: How to add constraint to only allow a particular set of numbers in a string column

Add a check constraint:

CONSTRAINT check_numbers CHECK (regexp_like(the_column, '^[67]+$')) 

This requires at last one digit.

Check constraint to make sure values in a character column are all digits

It hurts the brain, not NOT LIKE (not range) works.

SELECT 1 WHERE 'foo' NOT LIKE '%[^0-9]%'

SELECT 1 WHERE '123' NOT LIKE '%[^0-9]%'

SELECT 1 WHERE 'aa1' NOT LIKE '%[^0-9]%'

SELECT 1 WHERE '1bb' NOT LIKE '%[^0-9]%'

SELECT 1 WHERE NULL NOT LIKE '%[^0-9]%'

SELECT 1 WHERE '   ' NOT LIKE '%[^0-9]%'

--FAIL. Need extra check. Perhaps NULLIF
SELECT 1 WHERE '' NOT LIKE '%[^0-9]%'

Double negative and deals with any length

Check Constraint with letters and digits

You can use REGEP_LIKE() function such as

ALTER TABLE vehicule 
ADD CONSTRAINT chk_nr_vehicul 
CHECK ( REGEXP_LIKE (nr_vehicul,'[A-Z]{2}[0-9]{2}[A-Z]{3}') );

where the digits in the curly braces represent the length for each part for substrings forming the whole string of length 7.

Update : If you need to insert the values with some other patterns such as AB00BB and B000BBB along with the pattern of current sample value(AB00BBB), then use pipe oprators such as

ALTER TABLE vehicule 
ADD CONSTRAINT chk_nr_vehicul 
CHECK ( REGEXP_LIKE (nr_vehicul,'[A-Z]{2}[0-9]{2}[A-Z]{3}|[A-Z]{2}[0-9]{2}[A-Z]{2}|[A-Z]{1}[0-9]{3}[A-Z]{3}') );

How to use CHECK constraint to restrict non-numeric varchar2 (only alphabets)?

You didn't state your DBMS so I'm assuming PostgreSQL

CHECK(name ~ '^[^0-9]*$')

Regular expressions in check constraint: does not accept numbers

As for the question "is there a better way to do it" regarding this constraint:

CHECK (NOT REGEXP_LIKE(first_name,'[[:digit:]]'))

It's a subjective question, so here's a subjective answer: I don't think so. It does the job, it's character-set safe, and it's easy to read (for a regex).

How to allow only numbers in (Oracle SQL)

First, unit_price IS a number. There is no need to check that is is a number; the data type takes care of that.

The basic definition for email would be:

email varchar2(255) default 'unknown@gmail.com' check (email like '%@%.%')

Here is a db<>fiddle.

I strongly advise you to not use a default like that, though. NULL makes much more sense in this situation.

---

Related Topics