Validate a column on database level in PL SQL

Question

I have a requirement where I want to validate a column value which will be something like below.

Below is the correct value for that column.

INAPDNCHXXXXTW6002

and the query for getting that value is below

SELECT RJ_NETWORK_ENTITY_ID FROM NE.STRUCTURE WHERE RJ_SAPID = P_SAPID;

So, how can I do this in database level

Why do you need to do this in PL/SQL instead of, say, in a database constraint? — Boneist
– Boneist, Commented Sep 8, 2017 at 11:03

Oto Shavadze · Accepted Answer · 2017-09-08 09:38:35Z

1

One way to check that value match your requirements, is to use regexp:

REGEXP_LIKE(col, '^[a-z0-9]{18}$', 'i') ;

If you want check that RJ_NETWORK_ENTITY_ID column meets those requirements, you can use:

SELECT RJ_NETWORK_ENTITY_ID FROM NE.STRUCTURE 
WHERE 
RJ_SAPID = P_SAPID 
and 
REGEXP_LIKE(RJ_NETWORK_ENTITY_ID, '^[a-z0-9]{18}$', 'i');

answered Sep 8, 2017 at 9:33

Oto Shavadze

43.3k56 gold badges169 silver badges248 bronze badges

Sign up to request clarification or add additional context in comments.

1 Answer 1