LIKE OR NULL with Multiple AND

  • I have this table and I'm writing VB.Net code to find the error based on supplied TAG/s, here is my initial T-SQL query which i know is horrible  I'm looking for some help on rewriting this so it actually works.

    Thanks

    Madaxe

    SELECT
                ERROR_MESSAGE
          FROM
                LEKO.UI_ERRORS
          WHERE
                  (UPPER(TAG1) LIKE UPPER('%New%') OR TAG1 is null)
          AND      (UPPER(TAG2) LIKE UPPER('%Passwords%') OR TAG2 is null)
          AND      (UPPER(TAG3) LIKE UPPER('%Dont%') OR TAG3 is null)
          AND      (UPPER(TAG4) LIKE UPPER('%Match%') OR TAG4 is null)
          AND      (UPPER(TAG5) LIKE UPPER('') OR TAG5 is null)

    ID    OBJECT_ID    TAG1    TAG2    TAG3    TAG4    TAG5    ERROR_MESSAGE    FK_OBJECT_ID_MOD_USER    MOD_DATE
    2    89    User    Login    Field    Empty        Not all Login Fields were Populated.    1    21-MAR-19
    3    91    Password    Failed    Reset            Password Failed to Reset.    1    23-MAR-19
    4    92    Password    Reset    Successful            Password Reset Successful.    1    23-MAR-19
    5    93    New    Password    Must    Differant        New Password Must be Differant.    1    23-MAR-19
    6    94    New    Password    Dont    Differant        New Passwords Dont Match.    1    23-MAR-19
    7    95    You    Must    Logged    Reset    Password    You Must be Logged in to Reset Password.    1    23-MAR-19
    8    96    One    New    Password    Fields    Empty    One of the New Password Fields Were Empty.    1    23-MAR-19

  • Why not set the collation to case-insensitive instead of doing all those converting to upper case? But without some sample data, and a sample of query parameter values, it's hard to say.

  • pietlinden - Saturday, March 23, 2019 2:44 PM

    Why not set the collation to case-insensitive instead of doing all those converting to upper case? But without some sample data, and a sample of query parameter values, it's hard to say.

    Changing the collation in a WHERE to a case-insensitive one won't help with performance here. If the OP wants to do case insensitive searches they should either add a persisted computed column with the value in a different (case insensitive) collation, or with the the entire value in upper case.

    The OP has actually supplied some sample data here, however, it it completely unreadable. Looking at the data, there doesn't appear to be enough columns in some of the rows; making it useless. Perhaps, ma_jeeves, you could instead post it as DDL and DML; or otherwise format the data so that it is properly aligned (and include your NULL values, as your query implies you have them).

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
    Larnu.uk

  • Also, there is no purpose to writing UPPER(<some constant string> ), especially if the string doesn't contain alphabetic characters.  

    UPPER('%New%') is the same as '%NEW%'
    UPPER('%Passwords%') is the same as '%PASSWORDS%'
    UPPER('%Dont%') is the same as '%DONT%'
    UPPER('%Match%') is the same as '%MATCH%'
    and UPPER('') is the same as LOWER('') is the same as ''.

    Drew

    Edited to remove the auto-smiley.

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply