0

while doing select in MS SQL very often NULL and '' can be treated identical. There is good description to Combine These: how to use null or empty string

However I wonder if it make sense to put this into a user defined function to simplify queries for this. Does anybody uses such function? Are there strong pro and cons on this one?

Volker
  • 447
  • 1
  • 5
  • 19
  • 3
    Putting this is a UDF (as simple as it is) could be a performance killer – John Cappelletti Jan 21 '18 at 00:04
  • 1
    It depends on your data as well. In some cases `''` and *`NULL`* may not represent the same thing. As a **very** simple idea, you could have a Yes/No question on your website/application. Values of `''` could represent that the customer has not answered the question, when it was presented to them, however, *`NULL`* may represent that the customer hasn't answered and it isn't applicable, or they haven't been given the opportunity (perhaps they registered before the question was added). I would never say yes `''` and *`NULL`* represent the same thing without knowing the data they represent. – Thom A Jan 21 '18 at 11:20
  • 2
    @JohnCappelletti also raises a good point. I no doubt imagine the "easiest" way to add a UDF for this would be to use a Scaler, as it goes in the `WHERE`. Scalar functions, however, are notoriously slower compared to Table-Value Function, but then it could look messy in your `FROM` clause. Personally, if you know that for that column *`NULL`* also means `''` use an `OR` clause: `AND (YourColumn = '' OR YourColumn IS NULL)`. Or, otherwise, if you don't wants NULLs, then use an `UPDATE` statement to change the *`NULL`* values to `''` and then set the column to be non-NULLable. – Thom A Jan 21 '18 at 11:25

0 Answers0