如何使用 SQL 分析 Oracle 数据库列值以了解列中存储的值类型

Question

我有一个要求，我必须分析/检查 Oracle 数据库表中的特定列是否仅包含字符（城市等列中不允许包含数字）。类似地，我必须检查列是否可以包含字母数字值，但不能包含特殊字符。如何使用 oracle sql 来实现这一点？

Answer 1

如果您使用的是 10g+ 版本，您可以尝试使用 regexp_instr 函数并搜索所需 posix 字符类的出现。

示例：

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

这应该仅返回“onlyletters”列没有数字的行，在本例中是那些具有值“a”和“b”的行。

如果您想查找包含数字的内容，请搜索 REGEXP_INSTR(...) > 0.

我不确定特殊字符是什么意思，但是您可以通过这种方式使用不同的posix字符类，我用它来检测带有“REGEXP_INSTR(onlyletters, '[[:cntrl:]]'的控制字符）”。

有关字符类的更多信息，请参见：http://www.regular-expressions.info/posixbrackets.html

Answer 2

这是使用 TRANSLATE 的示例。您可能需要对您希望允许的字符和您不允许的字符进行一些调整，但这会给您一个想法：

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

这是一个使用正则表达式的字符：

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

问候，
抢。