testing if value is null

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

Re: testing if value is null

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