How do I get around case sensitive fields when in SQL

Question

My code is:

CURSOR get_party_description is      
      select party_name  
      from   ifsapp.IDENTITY_PAY_INFO_ALL
      where  party_type    = :NEW.PARTY_TYPE
      and    identity   = identity_

:NEW_PARTY_TYPE = 'SUPPLIER' while the value in the field is 'Supplier'. This code will pull back no records but if I change it to 'Supplier', it finds the record

How do I change to search with out matching the case?

DB2_Philip · Answer 1 · 2017-01-24T17:32:55.417

2

You can convert both the variable and the field to upper or lower case.

where  UPPER(party_type)    = UPPER(:NEW.PARTY_TYPE)

This might cause a table space scan as the index on the field would be Case sensitive.

you can get around this by adding a generated column that is upper case and indexing that.

edited Jan 24 '17 at 17:32

answered Jan 24 '17 at 17:29

DB2_Philip

81
3

thank you! Not sure why I didn't think of that. I was over thinking this – Stephanie Strohmenger Jan 24 '17 at 17:31
Also consider the use of collation, see http://stackoverflow.com/questions/1831105/how-to-do-a-case-sensitive-search-in-where-clause-im-using-sql-server – Horaciux Jan 24 '17 at 18:53

score 0 · Answer 2 · answered Jan 24 '17 at 17:25

0

Change both of your values to upper case. Example:

CURSOR get_party_description is      
      select party_name  
      from   ifsapp.IDENTITY_PAY_INFO_ALL
      where  UPPER(party_type)    = UPPER('SUPPLIER')
      and    identity   = identity_

answered Jan 24 '17 at 17:25

NicoRiff

4,803
3
25
54

score 0 · Answer 3 · answered Jan 24 '17 at 17:35

Besides converting both strings to the same case (upper- or lower-) and then comparing them for equality, most SQL dialects allow one to do a case-insensitive comparison by using the LIKE operator, as follows:

CURSOR get_party_description is      
    select party_name  
    from   ifsapp.IDENTITY_PAY_INFO_ALL
    where  party_type LIKE :NEW.PARTY_TYPE
    and    identity   = identity_

How do I get around case sensitive fields when in SQL

3 Answers3