How to enable AI(accent insensitiv) for one column without changing session
来源:互联网 发布:cfa报名 知乎 编辑:程序博客网 时间:2024/04/30 22:14
Following is my test SQL:
create table accent_test(col varchar2(10 char);
insert into accent_test values('INDIA');
insert into accent_test values('india');
insert into accent_test values('InDiÄ ');
Following SQL can only enable CI(Case insensitive), but can’t enable AI(accent insensitive):
select *
from accent_test
where NLS_UPPER(col, 'NLS_SORT=GENERIC_BASELETTER') LIKE NLS_UPPER('INDIA', 'NLS_SORT=GENERIC_BASELETTER')
COL
INDIA
india
Upper SQL can’t return the third row ‘InDiÄ’ which includes accent character.
I know following SQL can enable AI by changing the session. However it will change the whole session and other columns in one table:
SQL> ALTER SESSION SET NLS_COMP=LINGUISTIC;
SQL> ALTER SESSION SET NLS_SORT=GENERIC_BASELETTER;
Following lists two solutions I can find out to enable CIAI without changing the session. However, both of these two solutions have limitations:
1. where upper(convert(<CIAI_column>, 'JA16SJIS')) like upper(convert(<search_string>, 'JA16SJIS'))
Because the DB's character set is AL32UTF8, I didn't know whether convert(<CIAI_column>, 'JA16SJIS') may return messy code '?' for the characters which are not from character set JA16SJIS.
2. use Equivalence Class '[= =]' in Regular Expressions. A base letter and all of the accented versions of the base constitute an equivalence class. For example, the equivalence class [=a=] matches ä as well as â.
Example SQL: 'SELECT col FROM accent_test WHERE REGEXP_LIKE(col, '[[=I=][=N=][=D=][=I=][=A=]]*','i');'
However, may cause performance issue. Even a index on accent_test(col) is created, we can't avoid full table scan.
- How to enable AI(accent insensitiv) for one column without changing session
- How to Enable SQL_TRACE for Another Session or in MTS Using Oradebug(文档 ID 1058210.6)
- How to change SYSDATE's value to a static date without changing system date
- Sort the array one by one without changing current array
- How to enable packet forwarding for IPv4 and IPv6
- How to enable visual inheritance for Developer Express controls
- How to enable file sharing for my app?
- How to Enable Failed Request Tracing for UNC Hosting
- How to enable web condor?
- How to enable navigation arrows
- How to enable the debugfs
- How to Enable Multi-Touch
- How to define multiple controllers for one view in angularJS?
- How to Enable Plain Text Password on Windows for Connecting to Samba Server
- How To Quickly Add/Remove Column Statistics (Histograms) For A Column (文档 ID 390249.1
- How to read one column from SQLite and store in Array
- Android 如何在自定义界面上启用输入法 (How to enable inputmethod for the custom UI)
- Linux: How to enable root user in Gnome for VirtualBox Fedora?
- cocos2d-x 随机数
- Python简明教程学习笔记[三]
- 385. Success belongs to persevering. 坚持就是胜利
- 康众平板探测器
- SSH中集成P6SPY,irontrack sql,sqlprofiler
- How to enable AI(accent insensitiv) for one column without changing session
- Revoke oracle public privleges
- 设计模式一之Singleton模式
- Xen的源码安装及dom0,domU的设置
- 女子为父筹钱治病舍身遭骗色究竟该怪谁?
- 这段耻辱的经历让我再也无法快乐
- Oracle trace & tkprof
- Oracle无法启动1——ORA-12541:TNS:无监听程序
- leetcode 68: Interleaving String