Results 1 to 7 of 7
  1. #1
    Join Date
    Jun 2009
    Posts
    3

    Unanswered: Difference between UPPER(?) and UPPER(TEXT)

    hi,

    hi i need to know the difference for the following

    in my query where clause to make my search case insensitive i used

    UPPER(Customer_name)=Upper(?) .

    is it advised and good way to use or i need to put a variable and assigning with value for comparison ex

    String name = object.getCustName();
    UPPER(customer_name)=Upper(name);

    which one efficient and why?
    plz clarify . thnx in advance.

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    using UPPER(customer_name) in the query is a performance killer ... Can you add a new column customer_name_upper to the table and make it GENERATED ALWAYS UPPER(cutomer_name)?

    and then you can do
    String name = object.getCustName();
    String nameUpper=name.toUpperCase();
    and in the query
    customer_name_upper=nameUpper


    HTH

    Sathyaram
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  3. #3
    Join Date
    Jun 2009
    Posts
    3
    thanks sathya,
    but the Table is an existing one and i can't touch it.
    i need a option which will be efficient and also improve performance.

    when i used UPPER(?) the query ran for 244ms and when i gave below from ur solution

    String nameUpper=name.toUpperCase();
    Upper(customer_name)=nameUpper; it took 150ms
    so i opted this

    thanks !!!

  4. #4
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    I assume you will be using prepared statements ...

    If not, consider it for performance improvement

    Sathyaram
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  5. #5
    Join Date
    Nov 2005
    Location
    IL
    Posts
    557
    We never have enough time or budget to fix the data. But we have all of the above and then some to find a costly solution.

    Adding another column is always an option, but if you are transforming that column to UPPER case all of the time or most of the time. Then instead of adding new column or creating a work around you should get the data changed.

    Reload the data using UPPER case and make sure that all new data coming in is in UPPER case. It would be a preferred method, at least to me Fix the data not the process.
    --
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows

    DB2 v9.7.0.6 os 6.1.0.0

  6. #6
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Cougar,

    I agree with your comment ..

    But there are exceptions - when you want to capture and return the data in the same way the user input or sometimes, the data has to be in the right case Eg. Surname - McDonald or McDONALD and not MCDONALD.

    I think this is more a business decision than a technical one ... Of course, business decisions do change if there is a cost associated with it

    Cheers

    Sathyaram

    We never have enough time or budget to fix the data. But we have all of the above and then some to find a costly solution.

    Adding another column is always an option, but if you are transforming that column to UPPER case all of the time or most of the time. Then instead of adding new column or creating a work around you should get the data changed.

    Reload the data using UPPER case and make sure that all new data coming in is in UPPER case. It would be a preferred method, at least to me Fix the data not the process.
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  7. #7
    Join Date
    Nov 2005
    Location
    IL
    Posts
    557
    Quote Originally Posted by sathyaram_s
    Cougar,

    I agree with your comment ..

    But there are exceptions - when you want to capture and return the data in the same way the user input or sometimes, the data has to be in the right case Eg. Surname - McDonald or McDONALD and not MCDONALD.

    I think this is more a business decision than a technical one ... Of course, business decisions do change if there is a cost associated with it

    Cheers

    Sathyaram
    True, If that is the case and business has deep pockets or short sight then adding another column like you said is the way to go.
    --
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows

    DB2 v9.7.0.6 os 6.1.0.0

Posting Permissions

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