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 like Len() that counts white space

Good day to you, community friend!

Just a quick one - I swear I've seen a function before that was like Len() but it counted the white spaces too. I have a feeling it was something along the lines of "data_length" but I can't for the life of me find it!

Many thanks,
GeorgeSkip the underscore.|||I knew it was something that simple!!
Cheers keffenils :)|||I appear to have slipped a cog or two here. Can someone explain what georgev meant based on:DECLARE @.c VARCHAR(50)

SELECT @.c = 'This is a test'

SELECT Len(@.c), DataLength(@.c), @.c AS '12345678901234567890'-PatP|||DECLARE @.c VARCHAR(50)

SELECT @.c ='This is a test '

SELECTLen(@.c),DataLength(@.c), @.c AS'12345678901234567890'

:)|||Ah ha! I keep thinking like SQL Server, so trailing whitespace isn't really there in my mind... This was actually a user-required concession to make CHAR() columns function the way that microcomputer programmers expected them to, long before Microsoft inherited the product.

-PatP|||The trailing spaces are maintained in SQL Server CHAR columns. It is the LEN function that ignores them when returning a value.|||The trailing spaces are maintained in SQL Server CHAR columns. It is the LEN function that ignores them when returning a value.That's what I thought I said.

Somewhere prior to 4.0, spaces were left "as is" by functions, including trailing spaces. By the time that 4.21 was released, all of the string functions were supposed to trim trailing spaces before processing, and convert any string results back into the CHAR() type if that is what the function had originally been passed.

-PatP