Function similar to ISNULL()

I'm constructing a single string of several counts with concatenated labels using SQL and want to not show zeros (or their labels). Is there a function within an SQL statement that will let me do this? ISNULL() sort of does this, but I really need to test for zero instead of NULL to eliminate noise data from the string.You could write your own User Defined Function. Check out Books On Line for more info.
|||

Try the links below you may find T-SQL funtion to do what you need, but remember ISNULL can give just plain wrong numbers with OUTER JOIN in SQL Server, it is a known issue. Hope this helps.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_nos-nz_3uhy.asp

http://www.samspublishing.com/articles/article.asp?p=31283&seqNum=3&rl=1

function

Good Morning,
I have a question.
Is there a function like ISNULL but for the space value(example ISSPACE)?

THANKSHi

There is no space function but you can compare directly or something like

RTRIM(<fld>) = ''

RTRIM can be affected by the version or compatibility level of SQL Server
you are using.

The SPACE(n) function will create a string of n spaces.

For all available string functions check out books online or
http://msdn.microsoft.com/library/d..._fa-fz_7oqb.asp

John

"Killer" <roninkaiser@.tiscali.it> wrote in message
news:hFBxc.99850$Qc.3858785@.twister1.libero.it...
> Good Morning,
> I have a question.
> Is there a function like ISNULL but for the space value(example ISSPACE)?
> THANKS|||"Killer" <roninkaiser@.tiscali.it> wrote in message news:<hFBxc.99850$Qc.3858785@.twister1.libero.it>...
> Good Morning,
> I have a question.
> Is there a function like ISNULL but for the space value(example ISSPACE)?
> THANKS

It looks like you got an answer in another group - please do not post
to multiple groups independently.

Simon

function

Good Morning,
I have a question.
Is there a function like ISNULL but for the space value(example ISSPACE)?
THANKS
To substitute some other value instead of a space you could use CASE:
CASE WHEN x<>' ' THEN x ELSE 'something' END
Or you could use NULLIF() in conjunction with ISNULL() or COALESCE():
ISNULL(NULLIF(x,' '),'something')
COALESCE(NULLIF(x,' '),'something')
All of these examples return 'something' if X is either a NULL or a space.
David Portas
SQL Server MVP
|||In addition to David's response, no there is not a pre-defined function
ISSPACE, but you could write a scalar UDF in a couple of minutes...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Killer" <roninkaiser@.tiscali.it> wrote in message
news:nEBxc.46489$Wc.1502104@.twister2.libero.it...
> Good Morning,
> I have a question.
> Is there a function like ISNULL but for the space value(example ISSPACE)?
> THANKS
>

function

Good Morning,
I have a question.
Is there a function like ISNULL but for the space value(example ISSPACE)?
THANKSTo substitute some other value instead of a space you could use CASE:
CASE WHEN x<>' ' THEN x ELSE 'something' END
Or you could use NULLIF() in conjunction with ISNULL() or COALESCE():
ISNULL(NULLIF(x,' '),'something')
COALESCE(NULLIF(x,' '),'something')
All of these examples return 'something' if X is either a NULL or a space.
David Portas
SQL Server MVP
--|||In addition to David's response, no there is not a pre-defined function
ISSPACE, but you could write a scalar UDF in a couple of minutes...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Killer" <roninkaiser@.tiscali.it> wrote in message
news:nEBxc.46489$Wc.1502104@.twister2.libero.it...
> Good Morning,
> I have a question.
> Is there a function like ISNULL but for the space value(example ISSPACE)?
> THANKS
>

function

Good Morning,
I have a question.
Is there a function like ISNULL but for the space value(example ISSPACE)?
THANKSTo substitute some other value instead of a space you could use CASE:
CASE WHEN x<>' ' THEN x ELSE 'something' END
Or you could use NULLIF() in conjunction with ISNULL() or COALESCE():
ISNULL(NULLIF(x,' '),'something')
COALESCE(NULLIF(x,' '),'something')
All of these examples return 'something' if X is either a NULL or a space.
--
David Portas
SQL Server MVP
--|||In addition to David's response, no there is not a pre-defined function
ISSPACE, but you could write a scalar UDF in a couple of minutes...
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Killer" <roninkaiser@.tiscali.it> wrote in message
news:nEBxc.46489$Wc.1502104@.twister2.libero.it...
> Good Morning,
> I have a question.
> Is there a function like ISNULL but for the space value(example ISSPACE)?
> THANKS
>