Treat Integer field Zero like NULL

[RobHVB6Sql]

I have been struggling with this one for some time.
Several reports use this and I want to return the title (an integer field) where possible, but return a space for those with a 0 value.

Can anyone come up with code that acts like ISZERO? much like ISNULL?

SELECT 
'Client' = p.Surname + ', ' + p.Firstname + ISZERO(p.Title, '')
FROM tblPerson p

Desired results would be:
SMITH, Joe Mr
SMITH, Jennie
(Second row here has a title set to 0).

Rob Hasard
(VB6 /SQL 7.0)

 

[mrdenny]

A case statement. Or is you are going to use it often, write your own function called iszero. Something like this should do the trick.

create function iszero (@numb int)
returns varchar(20)
as
if @numb = 0
   return('')
else
   return(convert(varchar(20), @numb))

I'm not sure how slow this would be, but it should do the trick.

Denny

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(My very old site)

 

[RobHVB6Sql]

Great thanks, I just used a CASE statement, ie:

SELECT 
'Client' = p.Surname + ', ' + p.Firstname + 
     CASE p.Title
         WHEN 0 THEN ' ' 
         ELSE ' ' + p.Title
         END
FROM tblPerson p

Rob Hasard
(VB6 /SQL 7.0)

 

[vongrunt]

Another alternative is to set SET CONCAT_NULL_YIELDS_NULL to ON (useful when you have to concatenate lotsa strings) and convert dummy values to NULL with NULLIF(). But in this single case I'd also probably go with CASE statement.

 

[donutman]

Vongrunt, have you got a SQL reference manual in your bathroom? You must be one of those squatting readers.

-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[donutman]

Mrdenny, here's my rule on when to write a UDF like that. If you are going to use if often, then don't write it because it's going to be a significant resource drain. If it isn't going to be used to often, then why waste your time writing it?

-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[vongrunt]

Naya, just ability to remember rarely used things + mild allergy to any form of IF statement I developed these days .

Here is my rule: do SELECT surname, firstname, title FROM tblPerson then format results client-side.

 

[donutman]

Promulgated from the perspective of a purist's perfectionist...e[sup]2[/sup] must have infected you.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[mrdenny]

I did state that the udf could case slowless. I probably should have stated it more clearly.

I just like to empower people by giving them all the options that are available to them.

Denny

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(My very old site)

 

[donutman]

Oh, I was just trying to be funny. I knew where you were coming from.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]