CODEWITHSUNDEEP
oracle-database
user1194169
user1194169

Reputation: 53

How to apply constraint with any variable so that it will take only 5 numeric character in oracle

Suppose I want to create table ABC in which there is column namely UniqueAddress.

I want that it should take 30 characters in which it takes only 5 numeric character.

What is the correct query for it?

Upvotes: 2

Views: 102

Answers (2)

Ollie
Ollie

Reputation: 17538

Building upon on @Ben's answer, I'd use REGEXP_REPLACE instead of all the translating and replacing etc.

alter table <table_name>    
  add constraint unique_address_chk
check ( LENGTH(REGEXP_REPLACE(unique_address, '[^0-9]+', '')) <= 5);

It's neater but does the same job.

The regex effectively removes any non-numeric characters and then the length function returns the length of the sting (of numerics) left.

As for the column only holding 30 characters, you specify that when you create the column as a VARCHAR2(30 CHAR)

Hope it helps...

Upvotes: 2

Ben
Ben

Reputation: 52863

I think this is calls for translate, which can look scary but works wonders.. I hope that your column is already only 30 characters so you don't need to include this condition in the constraint.

Something like the following would work:

 alter table ABC
   add constraint abc_unique_address_chk
 check ( length(replace( translate( lower(unique_address)
                                   ,translate( lower(unique_address)
                                              ,'1234567890',' ')
                                   ,' ')
                        ,' ')
                ) <= 5);

This translates all numeric characters in the column to a space or nothing then giving you all other characters. I then use this list to translate all non-numeric characters to a space or nothing, replace the spaces and you're left with all the numeric.

 SQL> create table abc ( unique_address varchar2(30));

Table created.

SQL>
SQL>  alter table ABC
  2     add constraint abc_unique_address_chk
  3   check ( length(replace( translate( lower(unique_address)
  4                                     ,translate( lower(unique_address)
  5                                                ,'1234567890',' ')
  6                                     ,' ')
  7                          ,' ')
  8                  ) <= 5);

Table altered.

SQL>
SQL>  insert into abc values ( 'Hi, my name is Ben 12345');

1 row created.

SQL>  commit;

Commit complete.

SQL>  insert into abc values ( 'This has 6 numbers 12345');
 insert into abc values ( 'This has 6 numbers 12345')
*
ERROR at line 1:
ORA-02290: check constraint (INBOUND.ABC_UNIQUE_ADDRESS_CHK) violated


SQL>  commit;

Commit complete.

SQL>

Upvotes: 1

Related Questions