当要求汉字进行模糊匹配的时候,由于oracle是采用存储的字节流进行查找的,所以有存在着一个汉字的后半截和另一个汉字的前半截拼起来正好构成某个汉字。这样则存在着模糊匹配出现错误的可能性。碰巧,今天我在生产数据库应用中遭遇了好几个。
SQL> selectPRODUCTION_SERVICE fromcompany whereview_name = '泰兴市佰信纺织有限公司';
PRODUCTION_SERVICE
------------------------------------------------------------------------------------------------------------------------
牛仔布;色织布;弹力牛仔布;竹节牛仔布;彩色牛仔布;提花牛仔布;牛仔服;牛仔裤;纱卡;灯蕊绒;
SQL>
SQL> selectcount(*)
fromcompany
where view_name = '泰兴市佰信纺织有限公司'
and PRODUCTION_SERVICE like '%'||'蟹'||'%';
234
COUNT(*)
----------
1
SQL> select PRODUCTION_SERVICE, dump(PRODUCTION_SERVICE),dump( '蟹')
2fromcompany
where view_name = '泰兴市佰信纺织有限公司'
34;
PRODUCTION_SERVICE
------------------------------------------------------------------------------------------------------------------------
DUMP(PRODUCTION_SERVICE)
------------------------------------------------------------------------------------------------------------------------
DUMP('蟹')
---------------------
牛仔布;色织布;弹力牛仔布;竹节牛仔布;彩色牛仔布;提花牛仔布;牛仔服;牛仔裤;纱卡;灯蕊绒;
Typ=1 Len=84: 197,163,215,208,178,188,59,201,171,214,175,178,188,59,181,175,193,166,197,163,215,208,178,188,59,214,241,1
89,218,197,163,215,208,178,188,59,178,202,201,171,197,163,215,208,178,188,59,204,225,187,168,197,163,215,208,178,188,59,
197,163,215,208,183,254,59,197,163,215,208,191,227,59,201,180,191,168,59,181,198,200,239,200,222,59
Typ=96 Len=2: 208,183
SQL> select1 fromdualwhere'仔服'like'%'||'蟹'||'%';
1
----------
1
SQL>
原文参考
http://blog.itpub.net/post/330/3766
|