1

Is there a way to do a LIKE operation in DB2 against a varchar column in a case insensitive way, without changing the underlying column definition?

Or is there a workaround like SELECT * FROM mytable WHERE mycolumn LIKE '%string%' OR mycolumn LIKE '%STRING%' that would work?

2
1

No. Assuming Db2 for LUW, you can choose a case-insensitive collating table, but only at the database creation time.

You'll have to standardise the case for comparison, with obvious performance implications:

SELECT * FROM mytable WHERE LCASE(mycolumn) LIKE '%string%'
3
  • on Db2 for i at least, you can set your session to use a different collation...
    – Charles
    Feb 11 '20 at 17:31
  • @Charles Do you have a link that explains how to do that, set session collation? My google skills are failing me on this.
    – amber
    Apr 9 at 16:32
  • 1
    @amber you should ask this as a new question
    – Charles
    Apr 9 at 16:36
2

Try

SELECT * FROM mytable WHERE REGEXP_LIKE(mycolumn,'string',1,'i')

Will work if your Db2 version supports regular expression functions https://www.ibm.com/support/knowledgecenter/en/SSEPGG_11.5.0/com.ibm.db2.luw.sql.ref.doc/doc/r0061494.html and if you escape any regex meta-characters in your string

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.