optional search parameters in sql query and rows with null values

Question

Ok here is my problem :

Before i start the description, let me to tell you that I have googled up a lot and I am posting this question for a good optimal solution :)

i am building a rest service on WCF to get userProfiles... the user can filter userProfiles by giving something like userProfiles?location=London

now i have the following method

GetUserProfiles(string firstname, string lastname, string age, string location)

the sql query string i built is: select firstname, lastname, .... from profiles where (firstName like '%{firstname}%') AND (lastName like '%{lastName}%') ....and so on with all variables being replaced by string formatter.

Problem with this is that it filters any row having firstname, lastname, age or location having a null value....

doing something like (firstName like '%{firstName}%' OR firstName IS NULL) would be tedious and the statement would become unmaintanable! (in this example there are only 4 arguments, but in my actual method there are 10)

What would be the best solution for this?....How is this situation usually handled?

Database used : MySql

Answer 1

You can use COALESCE(value,...)

coalesce(firstName, '') like '%{firstname}%'

Answer 2

You can use COALESCE:

select firstname, lastname, ....
from profiles
where (coalesce(firstName, '') like '%{firstname}%')
AND (coalesce(lastName, '') like '%{lastName}%')

Answer 3

You could always disallow null values in the first place - after all, everybody must be alive for some positive number of years and be somewhere geographically.

If you absolutely must allow nulls, then either use COALESCE, as others have suggested, or possibly IFNULL which is MySQL specific and may be slightly more optimal since it takes exactly 2 paramters.