Reputation: 2000

Replace nulls values in sql using select statement in mysql?

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.

Upvotes: 22

Answers (5)

Reputation: 4415

select NVL(columnname, '#') as columnName from tableName
select NVL(email, '%') as email, NVL(city, '%') AS city FROM Account

Upvotes: 0

Reputation: 171371

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

Upvotes: 53

Reputation: 3044

I think you're looking for the IFNULL function IFNULL(field, 0) will return a 0 when the field returns null

Upvotes: 2

Reputation: 16325

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;

Upvotes: 6

Reputation: 3298

An UPDATE statement is needed to update data in a table. You cannot use the SELECT statement to do so.

Upvotes: 0