CODEWITHSUNDEEP
sql
Stephanie Strohmenger
Stephanie Strohmenger

Reputation: 21

How do I get around case sensitive fields when in SQL

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?

Upvotes: 0

Views: 46

Answers (3)

STLDev
STLDev

Reputation: 6164

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_

Upvotes: 0

DB2_Philip
DB2_Philip

Reputation: 81

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.

Upvotes: 2

NicoRiff
NicoRiff

Reputation: 4883

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_

Upvotes: 0

Related Questions