My sql query :
select * from users where email = email and password=password;
Result :
Expected Result :
I want to replace all null values with the empty string(" ").
-
I think, you need to write a wrapper that checks the values and make it empty for null values.Mukesh Sharma– Mukesh Sharma2016-07-11 07:21:42 +00:00Commented Jul 11, 2016 at 7:21
Add a comment
|
1 Answer
Example:
Using IFNULL:
SELECT
IFNULL(firstName,'') AS firstName,
IFNULL(lastName,'') AS lastName,
....
FROM YOUR_TABLE.
Using COALESCE:
SELECT
COALESCE(firstName,'') AS firstName,
COALESCE(lastName,'') AS lastName,
....
FROM YOUR_TABLE
Note:
The main difference between the two is that IFNULL function takes two arguments and returns the first one if it's not NULL or the second if the first one is NULL.
COALESCE function can take two or more parameters and returns the first non-NULL parameter, or NULL if all parameters are null
3 Comments
midhun k
i have 9 columns which holding the null value. if i use the above methods ,the codes go longer. Is there any alternative for that
1000111
If you want to skip this NULL checking in query level then you must have to do this somewhere else (e.g. Application level or client end). Another way could be updating the
Null values with empty string in your table and later altering the columns to NOT NULL1000111
If you allow
NULL value during insertion then I think you can approach this way I mean using COALESCE/IFNULL