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

2 Answers 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, 2020 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, 2021 at 16:32
  • 1
    @amber you should ask this as a new question
    – Charles
    Apr 9, 2021 at 16:36
3

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 and acknowledge you have read our privacy policy.

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