Join the social network of Tech Nerds, increase skill rank, get work, manage projects...
 
  • How to make WHERE col LIKE '%' select NULL values too?

    • 0
    • 1
    • 0
    • 1
    • 0
    • 0
    • 0
    • 0
    • 155
    Comment on it

    When we apply LIKE keyword to any query then it doesn't return NULL values, and sometimes it required to return NULL values as well. We can do this by using IS NULL or IFNULL operator.

    Example:

    We have a table "employee" as below:

      

      employee
        id first_name salary country
        .......................................................
        1 John        10000  Canada
        2 Chris       20000  NULL
        3 Max         30000  NULL
        4 Jenny       25000  Canada

    Use the IS NULL operator

    The below statement selects all employee with "country" containing pattern "can" in the "employee" table and NULL values as well:

       

        SELECT * FROM employee
        WHERE country is null or country LIKE '%can%';

    Result

       

        id first_name salary country
        .......................................................
        1 John        10000  Canada
        2 Chris       20000  NULL
        3 Max         30000  NULL
        4 Jenny       25000  Canada

    To get the records that do not match with the specified pattern we use NOT keyword as below, this statement selects all emplyee not having country containing pattern "can" and NULL values as well:

       

        SELECT * FROM employee
        WHERE country is null or country NOT LIKE '%can%';

    Result

       

        id first_name salary country
        .......................................................
        2 Chris       20000  NULL
        3 Max         30000  NULL

    Use the IFNULL operator

    The below statement selects all employee with "country" containing pattern "can" in the "employee" table and NULL values as well:

       

        SELECT * FROM employee
        WHERE IFNULL(country, '') like '' or country LIKE '%can%';

    Result

       

        id first_name salary country
        .......................................................
        1 John        10000  Canada
        2 Chris       20000  NULL
        3 Max         30000  NULL
        4 Jenny       25000  Canada

    To get the records that do not match with the specified pattern we use NOT keyword as below, this statement selects all emplyess not having country containing pattern "can" and NULL values as well:

       

        SELECT * FROM employee
        WHERE IFNULL(country, '') like '' or country NOT LIKE '%can%';

    Result

       

        id first_name salary country
        .......................................................
        2 Chris       20000  NULL
        3 Max         30000  NULL

    Hope this will help you :)

 0 Comment(s)

Sign In
                           OR                           
                           OR                           
Register

Sign up using

                           OR                           
Forgot Password
Fill out the form below and instructions to reset your password will be emailed to you:
Reset Password
Fill out the form below and reset your password: