MYSQL, syntax to convert null into '0.00'


What is the

Syntax to convert any null values to '0.00'
LVL 8
kingjelyAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Om PrakashConnect With a Mentor Commented:
select  DATE,
        STATION,
        VOUCHERS_REDEEMED,
        COALESCE( GROSS_SALES, '0.00' )
from Live_VIP_Report
0
 
AbarajCommented:
ISNULL (value, '0.00')
0
 
ina_donCommented:
Where do you want to do this? In the database? Or are you writing an application that is getting data from mysql that you want to appear as 0.00 where the record is null?
0
Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

 
kingjelyAuthor Commented:
To change any GROSS_SALES that are printing null to '0.00' using my example what would i need to do?



select  DATE,
        STATION,
        VOUCHERS_REDEEMED,
        GROSS_SALES,
       
from Live_VIP_Report
0
 
kingjelyAuthor Commented:
HI im doing this in the MySQL Query Browser.

I want the null to be '0.00'

DATE                    STATION            VOUCHER_REDEEMED      GROSS_VALUE
2010-01-01              1                      $250.00                                    500.00
2010-01-01              1                      0.00                                          null                    
0
 
Om PrakashCommented:
To SELECT

SELECT COALESCE( myname, '0.00' )
FROM `testTable`
WHERE 1
LIMIT 0 , 30

TO UPDATE


UPDATE testTable SET myname = COALESCE( myname, '0.00' )

0
 
InsoftserviceCommented:
hi,
select DATE,
        STATION,
        VOUCHERS_REDEEMED,
        if(GROSS_SALES = null,0.00,GROSS_SALES),
0
 
kingjelyAuthor Commented:
Hi this did it!

Thanks very much everyone.

0
 
InsoftserviceCommented:
hi,

what o/p did u got. please paste the o/p

select DATE,
        STATION,
        VOUCHERS_REDEEMED,
        if(GROSS_SALES = null,0.00,GROSS_SALES)
from Live_VIP_Report

0
 
kingjelyAuthor Commented:
Oh Crap, Insoftservice, you are also correct,

I should've given you points aswell, Hang on,

I'll ask another question
0
 
kingjelyAuthor Commented:
HI sorry insorfservice,

your query actually didnt work, it still returns null.

SOrry mate
0
All Courses

From novice to tech pro — start learning today.