#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2005
    Location
    Kansas City
    Posts
    27
    Rep Power
    0

    Question Replace NULL with something else during select?


    I searched six pages of NULL information, and learned a lot about NULL conditions in MySQL, but it didn't help me figure out my own desire.

    I'm performing a very simple query:

    SELECT * FROM MONSTER;

    The table returns with over 200 columns. Some values may be NULL. Is there an easy way to replace "NULL" with a string value, such as "NOTHING"?

    It sure would be sweet if there is!

    Upon Googling it, it looks like MSSQL has an "ISNULL(field, "replacewith") function. Just wondering if there's a way to do it within a MySQL query.
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Beginner (1000 - 1499 posts)

    Join Date
    Nov 2001
    Location
    North West UK
    Posts
    1,147
    Rep Power
    396
    Originally Posted by KsJayhawk
    Upon Googling it, it looks like MSSQL has an "ISNULL(field, "replacewith") function. Just wondering if there's a way to do it within a MySQL query.
    Code:
    IFNULL(field, "replacewith")
    9 out of 10 PHP problems can be resolved by setting
    PHP Code:
    error_reporting(E_ALL);
    ini_set('display_errors'1); 
    php -l <filename> will identify 9 out of the remaining 10 problems
    Remember, the command line is your friend

    Development Projects:
    PHPExcel
    PHPPowerPoint
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2005
    Location
    Kansas City
    Posts
    27
    Rep Power
    0
    Originally Posted by Mark Baker
    Code:
    IFNULL(field, "replacewith")
    Thanks. That's what I was fearing. There are over two-hundred columns to do this on. Do'h!!!!!!!!!!
  6. #4
  7. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Feb 2014
    Posts
    1
    Rep Power
    0

    Use COALESCE


    Use COALESCE(`Debit`,0) to replace all nulls with 0
    In this example the columns name is Debit

    It is the equivalent to ISNULL([Debit],0) In Ms Sql
  8. #5
  9. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,280
    Rep Power
    4279
    Originally Posted by KsJayhawk
    There are over two-hundred columns to do this on. Do'h!!!!!!!!!!
    with a good text editor, this is like thirty seconds of work
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  10. #6
  11. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,280
    Rep Power
    4279
    Originally Posted by KsJayhawk
    Upon Googling it, it looks like MSSQL has an "ISNULL(field, "replacewith") function. Just wondering if there's a way to do it within a MySQL query.
    in your extensive googling, did you run across the COALESCE() function?

    because that's the standard sql function and both mysql and microsoft sqlserver support it
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

IMN logo majestic logo threadwatch logo seochat tools logo