Oracle Sql Collation

I have a question about sql. I use jDeveloper and oracle sql developer. I wanna make search case insesitive and I wrote like this: String word = jTextField5.getText();

Solution 1:

COLLATE Latin1_General_CS_AS isn't an oracle syntax thing, it looks like a sql server thing

your basic sql could be:

ResultSetrs= statement.executeQuery("SELECT NAMES, AUTHOR, ID FROM BOOKS WHERE upper(NAMES) LIKE upper('%"+word+"%') OR upper(AUTHOR) LIKE upper('%"+word+"%') ");

but this is a full table/full index scan regardless, so won't be fast. for fast string searches, Oracle has oracle text. i'd suggest you read into that and implement a text index if you need to do these type of unbounded searches (on large tables).

Solution 2:

Another option would be to switch your session to use linguistic character comparison:

ALTER SESSION SET nls_comp = Linguistic;
ALTER SESSION SET nls_sort = XGerman_CI;

SELECT NAMES, AUTHOR, ID 
FROM BOOKS 
WHERE NAMES LIKE'%foo%'OR AUTHOR LIKE'%bar%'

This is a bit more advanced than "just" doing an UPPER on the values because it takes the language's rules into account (e.g. in German the ß is equivalent to ss

It will use a full table scan just as the solution using UPPER, but that's because of the % at the beginning of the search value - not because of the NLS setting (or applying the upper function. Both expressions could be supported by an index. Unlike PostgreSQL Oracle can never use an index for a %foo% condition, only for foo%.

For a list of available NLS_SORT values, see here

For a detailed explanation on how linguistic search and sorting works see here

Solution 3:

You need:

WHERE NAMES COLLATE Latin1_General_CS_AS LIKE '%"+word+"'% OR AUTHOR COLLATE Latin1_General_CS_AS LIKE '%"+word+"%', etc....