How can I specify a case insensitive search in Java

Question

I currently have this code in place:

query = "select ID_OBJECT from "+Config.props.getProperty("Ob.SchemaName.Objects")+".sdm_doc_info where name like ? and typefileverscurr = 'DWG'";

However this only returns results that match the upper case DWG. I would like my search to be case insensitive. How can I use ?i in this case? I tried the following but my Eclipse does not like it:

query = "select ID_OBJECT from "+Config.props.getProperty("Ob.SchemaName.Objects")+".sdm_doc_info where name like ? and typefileverscurr.matches('(?i:dwg)')";

Thanks for your help.

It would help if you could specify the type of database (DB2, MySQL, Oracle, etc). — Jim Garrison, Apr 01 '16 at 05:05
This really has nothing to do with Java; it's an SQL question. I've added a tag. — ajb, Apr 01 '16 at 05:09
I think your answer well be database-specific. For instance, MySQL ships by default with a case-sensitive collation, so you would not this problem with MySQL. Oracle and SQL Server are another story. — Tim Biegeleisen, Apr 01 '16 at 05:13
Apologies for confusion. This query is part of my java code. I am querying an Oracle SQL database. — jak, Apr 01 '16 at 06:42

score 1 · Answer 1 · answered Apr 01 '16 at 05:10

1

Usually I use to do these kind of jobs in two ways, One is by using the SQL upper() or lower() functions, like this:

"select ID_OBJECT from "+something+".sdme_doc_info where lower(typefileverscurr) = 'dwg'";

OR

"select ID_OBJECT from "+something+".sdme_doc_info where upper(typefileverscurr) = 'DWG'";

And another way is to use like in the query and mix above functions as well to make more insensitive

"select ID_OBJECT from "+something+".sdme_doc_info where lower(typefileverscurr) like '%dwg%'";

OR

"select ID_OBJECT from "+something+".sdme_doc_info where upper(typefileverscurr) like '%DWG%'";

answered Apr 01 '16 at 05:10

Shree Krishna

8,474
6
40
68

1

It might be better for him to handle this in the database by changing the collation. – Tim Biegeleisen Apr 01 '16 at 05:14
Yah you are right, he hasn't specified database too. – Shree Krishna Apr 01 '16 at 05:19
I have also tried something similar to the above, however it impacted the system performance quite considerably as it has to perform search twice. I am looking for something which can perform a predicate-like search which is case insensitive to the string supplied. – jak Apr 01 '16 at 06:27
To prevent that, you've to be sure that you stored data in database with only lower case or with only upper case. And you make get some tricks to use like query as well like see [here](http://stackoverflow.com/questions/2876789/case-insensitive-for-sql-like-wildcard-statement) in case of mysql. – Shree Krishna Apr 01 '16 at 06:40

How can I specify a case insensitive search in Java

1 Answers1