0

I have the following table in MS SQL Server:

Value  CustomerName                  Date  
------------------------------------------------------------------- 
1      Sam[Sammy]                    2016-09-30 11:13:15.550
2      Rimi                          2016-09-30 11:13:15.550

I am running the following query to fetch the data:

SELECT * 
FROM Table 
WHERE CONVERT(varchar, convert(date,BillDate), 112) = '20160930' AND 
      CustomerName LIKE isnull(NULL,CustomerName)

We have a Customer search form that has a Date field and a Customer Name field.The user has the option to search using the customer name but that's optional.

The above query is used to search for the customers using the Date field only. Now, when we run the query to search for customers it does not come up with the expected result. The CustomerName having the box brackets '[]' in their names are missing.

We cannot remove the box brackets in the names.

Is there a way to sort out this problem ?

Jaydip Jadhav
  • 12,179
  • 6
  • 24
  • 40
Bridget
  • 183
  • 1
  • 7
  • Why are you using `LIKE` for the comparison? Since you're using `LIKE`, you have to escape characters that have special meaning to `LIKE`. – Damien_The_Unbeliever Oct 04 '16 at 10:09
  • `LIKE isnull(NULL,CustomerName)` is the same as `CustomerName LIKE CustomerName`. I'm sure that is not what you mean? – Fred Oct 04 '16 at 10:14
  • Thanks for replying :)This is a search form, and I see 'LIKE' as the best option to search for the customer names. Is there any other way, of course leaving '=' ? If I have to escape characters how do I do that in this case ? – Bridget Oct 04 '16 at 10:14
  • 1
    @Bridget - once you're at the point of escaping characters, you'll realise that it will then only perform an exact match, and so it's just an overly complex way of not writing `=`. – Damien_The_Unbeliever Oct 04 '16 at 10:16

0 Answers0