testing if value is null

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

laredotornado - 31 Mar 2008 16:12 GMT

Hi,

How do I write a query where if a column, "value," is NULL I return
the phrase "No value entered" but otherwise return the column's value,
even if it is the empty string? I'm tried to modify this simple query

SELECT value FROM meta_data

Thanks, - Dave

Reply to this Message

Plamen Ratchev - 31 Mar 2008 16:43 GMT

You can use COALESCE:

SELECT COALESCE(value, 'No value entered')
FROM meta_data

It is important to note that COALESCE returns the higher precedence data
type from the parameters expressions, so this will work fine with character
columns but you will get conversion errors with numeric data types that have
higher precedence. See the example below:

SELECT COALESCE(value, 'No value entered')
FROM (SELECT 10.5
UNION ALL
SELECT NULL) AS T(value)

To fix you can cast the numeric value to character data type:

SELECT COALESCE(CAST(value AS VARCHAR(10)), 'No value entered')
FROM (SELECT 10.5
UNION ALL
SELECT NULL) AS T(value)

HTH,

Plamen Ratchev
http://www.SQLStudio.com

Reply to this Message

christopher.secord@gmail.com - 31 Mar 2008 19:47 GMT

> How do I write a query where if a column, "value," is NULL I return
> the phrase "No value entered" but otherwise return the column's value,
> even if it is the empty string? I'm tried to modify this simple query

You can also use a case statement.

select column1, column2,
value = case when value is null then 'No Value Entered' else value
end,
column4
from meta_data

Reply to this Message