CLOB 字段的大小写字符敏感的问题

最新推荐文章于 2025-11-25 12:10:59 发布

转载最新推荐文章于 2025-11-25 12:10:59 发布 · 953 阅读

文章标签：

#search #insert #integer #string #table #sql

本文介绍了一种在 Oracle 数据库中对 CLOB 类型字段进行不区分大小写的搜索方法。通过创建一个额外的列来保存 CLOB 字段的小写版本，并使用 DBMS_LOB.INSTR 函数在该小写版本上执行搜索，从而实现不区分大小写的查询。

1: case insensitive search with CLOB

Posted by Gautam Chowdhary on 09/13/02 05:18 AM

How can I do case insensitive search in CLOB field of oracle. There is
a functin named dbms_lob.instr, but it only helps for case sensitive
search, so if anybody have this kind of thing please let me know.

Thanks in advance

2: Response to case insensitive search with CLOB (response to 1)

Posted by Michael Bryzek on 09/13/02 11:35 AM

For a quick hack, you can create an additional column that is the lower case of the clob, and then use instr on the lower case version

create table keyword_test (
  id              integer primary key,
  contents        clob,
  contents_lower  clob
);


insert into keyword_test (id,contents) values (1, 'This is string ONE');
insert into keyword_test (id,contents) values (2, 'This is string TWO');
commit;

update keyword_test set contents_lower=lower(contents);
commit;

SQL> select id, dbms_lob.instr(contents_lower, 'one') 
       from keyword_test;

	ID DBMS_LOB.INSTR(CONTENTS_LOWER,'ONE')
---------- ------------------------------------
	 1				     16
	 2				      0

You can also use interMedia to provide keyword searching on the clob