IC4NOTICE: developerWorks Community will be offline May 29-30, 2015 while we upgrade to the latest version of IBM Connections. For more information, read our upgrade FAQ.
1 reply Latest Post - ‏2007-07-05T21:27:23Z by Stan
1525 Posts

Pinned topic selecting from varchar columns with ignore case

‏2007-07-04T08:15:07Z |
i found out that 'ilike' will ignore case but derby doesnt understand 'ilike'. I can come up with some workarounds like running select with all the possible uppercase/lowercase combinations of the searchstring but my searchstring can be very long and this might not be a good solution.

in short i want to learn how to write a query similar to this one.
select * from blabla ilike '%a%'

also accept my apologies if this has been asked before, searched here and google a bit but i couldnt find an answer.
Updated on 2007-07-05T21:27:23Z at 2007-07-05T21:27:23Z by Stan
  • Stan
    267 Posts

    Re: selecting from varchar columns with ignore case

    ‏2007-07-05T21:27:23Z  in response to SystemAdmin
    The simpilest way to to do this to apply the UPPER or LOWER function to the column and specify the appropriate translated value in the matching string. Example:

    ...where UPPER(LastName) like 'MC%'