excluding nulls from a table

Question

excluding nulls from a table

humbar-a

SSC Eights!

Points: 970
More actions
October 24, 2008 at 3:00 am

#200014

Hi,
I have a table which has some null values in "name" and "surname" columns. I use a function ti uppercase the first letter. But as i have nulls in the table i take an error when i use it. Here's the query :
SELECT idU, dbo.UppercaseFirstLetter(Name) AS Prenom, dbo.UppercaseFirstLetter(Surname) AS Nom
FROM Database
i tried to add "where name is not null and surname is not null" didn't work. The table is really big so i can't create a new table which does not have these columns.
Can anyone help me?

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

ck9663 Right there with Babe Points: 763 More actions · Answer 1

nailosuper (10/24/2008)
Hi,
I have a table which has some null values in "name" and "surname" columns. I use a function ti uppercase the first letter. But as i have nulls in the table i take an error when i use it. Here's the query :
SELECT idU, dbo.UppercaseFirstLetter(Name) AS Prenom, dbo.UppercaseFirstLetter(Surname) AS Nom
FROM Database
i tried to add "where name is not null and surname is not null" didn't work. The table is really big so i can't create a new table which does not have these columns.
Can anyone help me?

Try:

SELECT idU, dbo.UppercaseFirstLetter(isnull(Name,'')) AS Prenom, dbo.UppercaseFirstLetter(isnull(Surname,'')) AS Nom

FROM Database

You can also handle that inside your function.

-- CK

Greg-616990 SSC Enthusiast Points: 175 More actions · Answer 2

Could you post code of your function which uppercase the first letter.

krayknot SSCertifiable Points: 7998 More actions · Answer 3

nailosuper (10/24/2008)
Hi,
I have a table which has some null values in "name" and "surname" columns. I use a function ti uppercase the first letter. But as i have nulls in the table i take an error when i use it. Here's the query :
SELECT idU, dbo.UppercaseFirstLetter(Name) AS Prenom, dbo.UppercaseFirstLetter(Surname) AS Nom
FROM Database
i tried to add "where name is not null and surname is not null" didn't work. The table is really big so i can't create a new table which does not have these columns.
Can anyone help me?

I dont know about the code of your function. but you must use IsNull() to check the parameter value prior to have any processing on that

kshitij kumar
kshitij@krayknot.com
www.krayknot.com

Jerry Hung SSChampion Points: 12968 More actions · Answer 4

In your function

you can RETURN NULL if input is NULL, it's safer this way as well

Here is a sample

CREATE FUNCTION UppercaseFirstLetter

(@InputString VARCHAR (4000))

RETURNS VARCHAR(4000)

AS

BEGIN

IF @InputString IS NULL RETURN NULL

ELSE

....

END

SQLServerNewbieMCITP: Database Administrator SQL Server 2005

Gary Johnson-259336 SSCommitted Points: 1673 More actions · Answer 5

I agree with Jerry, it is better to change the function to handle the nulls. Changing the function to handle the nulls makes it so that if you pass in NULL it doesn't change the field to an empty string. Changing it to an empty string to me is wrong.

However if that isn't possible then you can use either ISNULL(field,'') or Coalesce(field, ''). I would probably use Coalesce as it is supported by the ANSI standard and ISNULL is a TSQL function. But each to their own. I would also wrap the results with a NULLIF to change the empty string back to a null.

SELECT

idU,

NULLIF(dbo.UppercaseFirstLetter(COALESCE(Name,'')),'') AS Prenom,

NULLIF(dbo.UppercaseFirstLetter(COALESCE(Surname,'')),'') AS Nom

FROM dbo.Table

Gary Johnson
Sr Database Engineer

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 6

I think it would be best that if the function receives a NULL, it should return a NULL. NULL has the special meaning of "unknown" and changing it to something other than NULL could lead to other problems down the line? What kind of problems? Dunno for sure, but anything that includes or excludes based on a NULL will now break because the data being returned isn't NULL anymore.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Goldie Lesser SSCertifiable Points: 6155 More actions · Answer 7

nailosuper (10/24/2008)
Hi,
I have a table which has some null values in "name" and "surname" columns. I use a function ti uppercase the first letter. But as i have nulls in the table i take an error when i use it. Here's the query :
SELECT idU, dbo.UppercaseFirstLetter(Name) AS Prenom, dbo.UppercaseFirstLetter(Surname) AS Nom
FROM Database
i tried to add "where name is not null and surname is not null" didn't work. The table is really big so i can't create a new table which does not have these columns.
Can anyone help me?

It's better to format text in your application, not the database.