cancel
Showing results for 
Search instead for 
Did you mean: 

Select query case insensitive for data type VARG

chinna_babu2
Active Participant
0 Kudos

Hi Experts,

I am having trouble in retrieving the results from a table using select query.

I have a table (Users) as below

Name (VARG) Number(INTEGER)

-


Murthy 0001

murthy 0002

-


when I am querying the table with select query as -


select * from Users where Name = 'Murthy'

this query is returning only one record which matches with capital letter of 'M', though both the record names are same.

I have seen in IBM forum to make the select query as case insensitive using the UPPER key word, I have tried this as below -


select * from Users where UPPER(Name) = 'MURTHY'

But this query is not working in my case...

My DB2 version is 8.1.5

Can any one please help in fixing this problem...

Thanks in Advance,

Murthy

Accepted Solutions (0)

Answers (4)

Answers (4)

chinna_babu2
Active Participant
0 Kudos

Resolved the issue.

0 Kudos

Unfortunately, you do not say, why the function UPPER does not work (what is the result / SQL error, ... ?). The 8.2 documentation points to function TRANSLATE. As alternative, use SQL function LOWER, or LCASE.

select * from users where lcase(name) = 'murthy'

Malte

chinna_babu2
Active Participant
0 Kudos

Hi All,

Thanks for your replies.

I have already tried all the options and none of them worked.

So, I have solved it by adding new columns to the table and which will have the Upper case of value.

Then I am querying the table by putting Upper Case column in to where condition.

Thanks,

Chinna.

Frank-Martin
Product and Topic Expert
Product and Topic Expert
0 Kudos

Hi Murthy,

your scenario worked fine on my DB2 LUW test database on version 9.5 .

> db2 " create table hugo ( c varchar(10) ) "

DB20000I The SQL command completed successfully.

> db2 " insert into hugo values ( 'Hallo' )"

DB20000I The SQL command completed successfully.

> db2 " select c from hugo where UPPER(c) = 'HALLO' "

C

-


Hallo

1 record(s) selected.

Is your question really related to DB2 LUW ? If yes, what are the column types of your test table?

Regards

Frank

JanStallkamp
Employee
Employee
0 Kudos

Does not look like a issue with DB2 for IBM i. Moved to DB2 for Linux, Unix, Windows forum.