testing if value is null

[laredotornado]

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

[Plamen Ratchev]

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(v alue AS VARCHAR(10)), 'No value entered')
FROM (SELECT 10.5
UNION ALL
SELECT NULL) AS T(value)

HTH,

Plamen Ratchev

http://www.SQLStudio.com

[christopher.secord@gmail.com]

Re: testing if value is null

On Mar 31, 10:12 am, laredotornado <laredotorn...@ zipmail.comwrot e:

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