How to profile oracle database column values using SQL for type of value stored in column

I have an requirement where i have to profile / check that a paricular column in an oracle database table contains only characters or not (numbers are not allowed in a column like city etc). Similary i have to check that a column can contain alpha numeric values but not special characters. How can t开发者_运维知识库his be accomplished using oracle sql.

If you are on version 10g+ you could try with the regexp_instr function and search for occurrences of the desired posix character classes.

Example:

create table test1(onlyletters varchar2(100))
/
insert into test1
values('a')
/
insert into test1
values('b')
/
insert into test1
values('a1')
/
insert into test1
values('22')
/

select *
from  test1
where REGEXP_INSTR(onlyletters, '[[:digit:]]') = 0

This should then return only the rows where the column 'onlyletters' have no digits, in this case those with values 'a' and 'b'.

If you want to find those that contains digits, search for REGEXP_INSTR(...) > 0.

I'm not sure what you mean by special characters, but you can use different posix character classes in this way, I've used it to detect control characters with "REGEXP_INSTR(onlyletters, '[[:cntrl:]]')".

More info about the character classes here: http://www.regular-expressions.info/posixbrackets.html

Here is an example using TRANSLATE. You might have to tweak a little with the characters you wish to allow and which you you don't, but it'll give you an idea:

SQL> create table cities
  2  ( name varchar2(30)
  3  , constraint city_name_alphanumeric
  4    check (translate(upper(name),'1ABCDEFGHIJKLMNOPQRSTUVWXYZ','1') is null)
  5  )
  6  /

Table created.

SQL> insert into cities values ('Utrecht')
  2  /

1 row created.

SQL> insert into cities values ('Utr3cht')
  2  /
insert into cities values ('Utr3cht')
*
ERROR at line 1:
ORA-02290: check constraint (RWIJK.CITY_NAME_ALPHANUMERIC) violated


SQL> insert into cities values ('UtrΘcht')
  2  /
insert into cities values ('UtrΘcht')
*
ERROR at line 1:
ORA-02290: check constraint (RWIJK.CITY_NAME_ALPHANUMERIC) violated


SQL> insert into cities values ('|Utrecht')
  2  /
insert into cities values ('|Utrecht')
*
ERROR at line 1:
ORA-02290: check constraint (RWIJK.CITY_NAME_ALPHANUMERIC) violated

And here is one using regular expressions:

SQL> delete cities
  2  /

1 row deleted.

SQL> alter table cities
  2    drop constraint city_name_alphanumeric
  3  /

Table altered.

SQL> alter table cities
  2    add constraint city_name_alphanumeric
  3    check (regexp_like(name,'^[[:alpha:]]+$'))
  4  /

Table altered.

SQL> insert into cities values ('Utrecht')
  2  /

1 row created.

SQL> insert into cities values ('Utr3cht')
  2  /
insert into cities values ('Utr3cht')
*
ERROR at line 1:
ORA-02290: check constraint (RWIJK.CITY_NAME_ALPHANUMERIC) violated


SQL> insert into cities values ('UtrΘcht')
  2  /
insert into cities values ('UtrΘcht')
*
ERROR at line 1:
ORA-02290: check constraint (RWIJK.CITY_NAME_ALPHANUMERIC) violated


SQL> insert into cities values ('|Utrecht')
  2  /
insert into cities values ('|Utrecht')
*
ERROR at line 1:
ORA-02290: check constraint (RWIJK.CITY_NAME_ALPHANUMERIC) violated

Regards,
Rob.