By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,379 Members | 1,380 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,379 IT Pros & Developers. It's quick & easy.

testing if value is null

P: n/a
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
Mar 31 '08 #1
Share this Question
Share on Google+
2 Replies


P: n/a
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

Mar 31 '08 #2

P: n/a
On Mar 31, 10:12 am, laredotornado <laredotorn...@zipmail.comwrote:
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
Mar 31 '08 #3

This discussion thread is closed

Replies have been disabled for this discussion.