Ho to do this? What query can be written by using select statement where all nulls should be replaced with 123?
I know we can do this y using, update tablename set fieldname = "123" where fieldname is null;
but can't do it using select statement.
-
update tablename set fieldname = "123" where fieldname is null;Thompson– Thompson2012-03-26 18:22:23 +00:00Commented Mar 26, 2012 at 18:22
-
I know this function, but how to do this replacement of null value using select statement ?Thompson– Thompson2012-03-26 18:23:34 +00:00Commented Mar 26, 2012 at 18:23
Add a comment
|
5 Answers
You have a lot of options for substituting NULL values in MySQL:
select case
when fieldname is null then '123'
else fieldname end as fieldname
from tablename
select coalesce(fieldname, '123') as fieldname
from tablename
select ifnull(fieldname, '123') as fieldname
from tablename
5 Comments
Thompson
yes, this is what i was looking for. thanks. Can be also write a query by using the select statement, where we want all NULL values to be at last using Order By.
epicsmile
This answer is useful for other databases as well. For example, both COALESCE and IFNULL are present in SQLite and work as expected.
Tyler
Note: IFNULL is for MySql alone. If you are using MS Access or SQL Server, then you are looking for ISNULL, and if you are using Oracle then you are looking for NVL. Source: w3schools.com/sql/sql_isnull.asp
D'Arcy Rittich
@Tyler Careful with blanket statements, there's ibm.com/support/knowledgecenter/en/SSEPEK_10.0.0/sqlref/src/tpc/…
Silas B
If anyone else is using ScannerVision (i.e. probably me from the future), use COALESCE.
There is a statement called IFNULL, which takes all the input values and returns the first non NULL value.
example:
select IFNULL(column, 1) FROM table;
http://dev.mysql.com/doc/refman/5.0/en/control-flow-functions.html#function_ifnull
1 Comment
Dave
This answers the question, but it's important to note that IFNULL only takes two arguments. COLAESCE (which is an MSSQL feature) takes an arbitrary number of input vales and returns the first non-null.
I think you're looking for the IFNULL function
IFNULL(field, 0) will return a 0 when the field returns null
Comments
An UPDATE statement is needed to update data in a table. You cannot use the SELECT statement to do so.
3 Comments
Dave
I think he wants to modify the output of the query, not the actual data.
frostmatthew
Ah, seems I misunderstood the question then :-(
Thompson
its ok Exupery. We have the answer from RedFilter
select NVL(columnname, '#') as columnName from tableName
select NVL(email, '%') as email, NVL(city, '%') AS city FROM Account
