MySQL Web Development

How to replace NULL values in a MySQL SELECT statement

Sometimes when you are SELECTing data from MySQL, some of your columns may have NULL data values and NULL’s don’t display well to end-users, so it may be best to replace the NULL’s with a string of text instead such as:

  • “Not defined”
  • “Undefined”
  • “Default value”

You could do this in PHP when you are echoing out your data, but it’s a lot easier to do it your MySQL statement.

For example if you have this simple SELECT statement, but not every manufacturers website is defined, so that the website column value is sometimes null.

SELECT id, manufacturer, website
FROM manufacturers

Then you can simply use the COALESCE function to check for and replace any NULL values with ‘Not defined’.

SELECT id, manufacturer, COALESCE(website, 'Not defined') AS website
FROM manufacturers

Also I’ve given the same column name as an alias to the function so your resulting recordset will have the same column header as your first query.

Did you find the above helpful? Please leave a comment below and let me know…