ConText

[GERPAT]

I'm trying to build a search engine which isn't case sensitive. Does any of you know how I can do non-case sensitive searching in oracle.

 

[karluk]

The easiest way to do this is to use the UPPER or LOWER function to convert your data to all upper or lower case when doing a search. For example

select text_col from your table
where upper(text_col) like '%ABC%'

would return rows that contain "abc", "ABC", "Abc", and so on.

You should also look into creating function based indexes to support this. Some case-insensitive queries will be much faster with a function based index.

Of course you also can look at implementing Intermedia Text, or some other Oracle add-on. The title of your post indicates that you're looking in that direction.

 

[vgg]

Hi,
I assume you give the search condition in the "where" clause.Then you can give your query as something like this:

select * from search_table

where lower(search_column) like 'lower(search_word)%';

You can create a function based index for lower(search_column) to improve performance.

regards,

VGG

 

[vgg]

Please modify the query:

select * from search_table

where lower(search_column) like lower('search_word%');


VG

 

[Jocsta]

Alternatively convert all alpha data to upper or lowercase before inserting it into the database!?
But really I would advise using function based indexes though.
Only use intermedia/context if you are searching large blocks of data (such as entire documents), or want to perform pattern matching within a sentence. It really isn't worth it if you are only searching for exact matches on small text columns.

<< JOC >>