Reputation: 13
Can someone help me to use the regular expression in sql for the following scenario
Need to check if any string/symbols(Other than number) value comes in for specific column then it should throw an error or anything. Currently have used
to_number(column) - but it is not throwing error if it only starts with string otherwise it is allowing.
Upvotes: 0
Views: 230
Reputation: 1271231
What you want is a check
constraint on the column. This would look like:
alter table t add constraint chk_t_col
check (regexp_like(col, '^[0-9]*$'));
A check
constraint will have the database validate the data for you. This is the best way to implement such a check to ensure data integrity.
Upvotes: 1
Reputation:
Check out below are the different scenario with REGEX as well as TO_NUMBER function.
REXEX valid Number scenario:
SELECT 1 FROM DUAL WHERE REGEXP_LIKE('1234', '^\d+(\.\d+)?$', '');
1234
REXEX invalid number scenario:
SELECT 1 FROM DUAL WHERE REGEXP_LIKE('AS1234#$', '^\d+(\.\d+)?$', '');
no data found
SELECT 1 FROM DUAL WHERE REGEXP_LIKE('$', '^\d+(\.\d+)?$', '');
no data found
TO_NUMBER valid scenario:
select to_number('123') from dual;
TO_NUMBER invalid scenario:
select to_number('123d') from dual;
select to_number('.23.') from dual;
select to_number('asdf') from dual;
ORA-01722: invalid number
Upvotes: 1