Case insensitive search in Java DB (Derby)

Question

I'm using Derby and I can't find a way to do case insensitive search.

For example, I have a table that I'm searching that contains "Hello" but I put a search query in for "hello" and at the moment I won't get a result, but I want to.

I can't find the correct syntax for it.

Sara

score 9 · Accepted Answer · answered Jul 15 '11 at 13:24

9

You can use the UPPER() or LOWER() SQL functions on both your search argument and the field, like in

SELECT *
FROM   mytab
WHERE  UPPER(lastname) = UPPER('McDonalds')

answered Jul 15 '11 at 13:24

MikeD

8,861
2
28
50

but this ignores the index probably set on the columns?! – dermoritz Feb 02 '12 at 08:53

score 3 · Answer 2 · answered May 23 '11 at 03:10

3

The most common way to do this is to use generated columns. Here's a nice writeup from one of the Derby developers: http://blogs.oracle.com/kah/entry/derby_10_5_preview_generated

answered May 23 '11 at 03:10

Bryan Pendleton

16,128
3
32
56

Good finding! not sure what is the performance gain though opposed to the simple UPPER to the search query – Marc Dec 25 '11 at 01:44
A better link would be useful here. That link now takes me to a main page, and searching for the page didn't find it. – MiguelMunoz Oct 31 '17 at 06:49
Sorry, that blog entry is close to 10 years old, and it looks like Oracle have removed it. :( You can still find it here, for now: https://bigip-blogs-cms-adc.oracle.com/kah/entry/derby_10_5_preview_generated Or, try using archive.org to access the page as it was in 2009... – Bryan Pendleton Oct 31 '17 at 13:38

score 0 · Answer 3 · answered Oct 07 '19 at 21:53

This is really a late answer which I am not sure is the best way to manage the the process of doing a case insensitive search on a Textarea in a Derby Database
We added our search results to a TableView so if you get two results returned then the user has at least the option to decide which result is of value.

    String upper = "BIG";
    String lower = "big";
    ResultSet rs = stmnt.executeQuery("SELECT * FROM Child WHERE cEntry LIKE 
    '"+"%"+upper+"%"+"'OR cEntry LIKE'"+"%"+lower+"%"+"'");

Case insensitive search in Java DB (Derby)

3 Answers3

Linked