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?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

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
Om PrakashCommented:
select  DATE,
        STATION,
        VOUCHERS_REDEEMED,
        COALESCE( GROSS_SALES, '0.00' )
from Live_VIP_Report
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.