Regular Expression in PLSQL

Question

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.

Answer 1

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.

Answer 2

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