Retrieve Case Insensitive SQL Select Query using DB2

Question

Below sql select query retrieves the data from table.

SELECT DISTINCT  FRUIT_NAME,FRUIT_ID  FROM Fruits ORDER BY FRUIT_NAME ASC

I have used "ORDER BY FRUIT_NAME ASC" which fetches the results based on Assending order as shown below.

Results :

Apple

Bannana

Mango

apricots

blueberries

I want a case insensitive search results like below.

Apple

apricots

Bannana

blueberries

Mango

I dont want to use upper() or lower() as it might affect performance for larger table data.

Also COLLATE NOCASE doesn't work with db2. Below is the error i got when i used COLLATE NOCASE in select sql query in db2.

1) [Code: -104, SQL State: 42601]  ILLEGAL SYMBOL "COLLATE".

Is there any way to retrieve case insensitive data from sql select query using db2?

What is your Db2-server platform (Z/os, i-series, Linux/Unix/Windows)? What is your Db2-server version? These facts should always be in your question. — mao
Thanks for the reply. I am using DB2 10.0.22 ,platform is windows. — coder12349
your question is frequently asked. Do your research. Common approaches include using generated-columns (and ordering by that generated-column), or using index-extensions, or order by collation_key_bit() function with suitable arguments for your language/territory. Example links developer.ibm.com/articles/making-db2-case-insensitive and ibm.com/developerworks/data/library/techarticle/dm-0712stolze/… — mao

Tim Biegeleisen Tim Biegeleisen · Accepted Answer · 2019-10-08T08:51:03

You could order using the LOWER function:

SELECT DISTINCT FRUIT_NAME, FRUIT_ID
FROM Fruits
ORDER BY LOWER(FRUIT_NAME);

Of course, this means the ORDER BY step can't use an index. If you need to order rapidly regardless of case, you could consider storing an all-lowercase version of the column, as one option.

Retrieve Case Insensitive SQL Select Query using DB2

1 Answers