Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2010
    Posts
    5

    Post Unanswered: Case Insensitive Queries:

    Hi, I use DB2 for AS/400.

    Right now we use iSeriesNavigator v5r4.

    When I run this sample query I get no results:

    SELECT *
    FROM MyTable
    WHERE LastName = 'smith'

    But this will return many results:

    SELECT *
    FROM MyTable
    WHERE LastName = 'Smith'

    I've tried adding SortSequence/SortType to my connection string (I'm using ASP.Net) and its still case sensitive. Any recommendations?

  2. #2
    Join Date
    Jun 2007
    Location
    germany
    Posts
    155
    WHERE UCASE(LastName) = 'SMITH'
    or
    WHERE UPPER(LastName) = 'SMITH'
    or
    WHERE LCASE(LastName) = 'smith'
    or
    WHERE LOWER(LastName) = 'smith'
    Dick Brenholtz, Ami in Deutschland

  3. #3
    Join Date
    Oct 2010
    Posts
    5
    Quote Originally Posted by dbzTHEdinosaur View Post
    WHERE UCASE(LastName) = 'SMITH'
    or
    WHERE UPPER(LastName) = 'SMITH'
    or
    WHERE LCASE(LastName) = 'smith'
    or
    WHERE LOWER(LastName) = 'smith'
    That could work, but this just seems quite messy to me! Is there a smoother way to tackle this?

  4. #4
    Join Date
    Jun 2007
    Location
    germany
    Posts
    155
    WHERE LastName in ('Smith', 'smith' ...
    would be more 'performance oriented' as there would be no function against every row.
    Dick Brenholtz, Ami in Deutschland

  5. #5
    Join Date
    Jul 2009
    Posts
    150

    Lightbulb From = to like

    When I met the same problem 2 years ago and try to solve by

    Code:
    Where UCASE(LastName) = 'SMITH'
    it was created the big performance issue.

    After this I found the solution without any performance affect:

    Code:
    Where (LastName like 's%' or LastName like 'S%')
                and Ucase(LastName) like '_MITH'
    Kara

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •