oracle 数字对比,在oracle中将数字作为字符串进行比较

这就是你要找的吗?

create table address as

select

'01 park avenue' address1,

'20 golden gate' address2,

'30 test' address3

from

dual;

insert into address

select

'01 park avenue' address1,

'20 golden gate' address2,

null address3

from

dual;

insert into address

select

'01 park avenue' address1,

'20 golden gate' address2,

null address3

from

dual;

commit;

下面是一个查询,它将通过按连接的数字字符串排序来查找“重复项”。我们使用地址连接上的regexp_replace从地址中提取数字。

select

address1 || address2 || address3 address_concat,

regexp_replace(address1 || address2 || address3, '[^[:digit:]]')

address_numbers_only

from

address

order by

address_numbers_only;

如果要查找与特定地址匹配的项,请尝试以下操作:

select

*

from

address

where

regexp_replace(address1 || address2 || address3, '[^[:digit:]]') =

regexp_replace(:v_address1 ||

:v_address2 ||

:v_address3, '[^[:digit:]]');

例如:

select

*

from

address

where

regexp_replace(address1 || address2 || address3, '[^[:digit:]]') =

regexp_replace('01 park avenue' ||

'20 golden gate' ||

null, '[^[:digit:]]');

-- returns...

ADDRESS1 ADDRESS2 ADDRESS3

01 park avenue 20 golden gate

01 park avenue 20 golden gate