Results 1 to 3 of 3

Thread: LIKE problem

  1. #1
    Join Date
    Mar 2008

    Unanswered: LIKE problem

    SELECT First_name, Last_name, Phone_number, Address
    FROM contact
    WHERE First_name LIKE @first + '%'
    AND Last_name LIKE @last + '%'
    AND Phone_number LIKE @phone + '%'
    AND Address LIKE @address + '%'
    I'm trying to filter contacts table. User can Filter by any combination of FirstName, LastName, Address and Phone number.

    Above example doesn't show if some fields of contact is NULL. It works okay if empty but doesn't work on NULL.

    First_name -- Last_name -- Phone_number -- Address
    AB            CD           NULL            ABC
    For instance let's say above data is in DB. If use search for @first = AB and all the other parameters empty. It doesn't show above data since phone_number is not empty but NULL

    Is there any recommendation?

  2. #2
    Join Date
    Apr 2002
    Toronto, Canada
    the best solution is

    1) test @phone

    2) if it's empty, do not include the phone condition in the WHERE clause

    vwalah!! | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Mar 2008
    I solved to issue by using ISNULL

    ISNULL(first_name, '') like @first + '%'
    But yours make more sense to me. I will change it now. Thank you so much. Glad I asked.

Posting Permissions

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