checking null value

Hi all,
I know someone answered this in the past but couldn't find the post.
How do I check for nulls if they might be in the column? colb and colc might
have null value?
the below query returns some rows that don't contain value "blue" which is
wrong.
select cola,colb,colc from tableA where
cola = 'blue' or
colb = 'blue' or
colc = 'blue'
thanks
gv>> How do I check for nulls if they might be in the column? colb and colc
Nulls can be checked using IS [NOT] NULL clause in SQL.
Since you are using OR operator, the query might return every row where any
one of cola, colb, col3 or any combination of the three has a value 'blue'.
Based on the query you posted, the result you see is valid.
Anith

Checking for Nulls in String

Hi all,

I have the following SQL string;

SELECT Title +' '+ FirstName +' '+ MiddleName +' '+ LastName AS PersonsName FROM MyData...Contacts;

I want to build the persons name into a single string, but all the fields are not always filled in, can anyone tell me the best way to build this so I don't have extra blank spaces when I don't need them?

Basically I need to check if the field has a value, if it does include it, if not leave it out.

Thanks

You have two option use COALESCE or ISNUL in you code, try the link below for details. Hope this helps.

http://hkmicc.wordpress.com/2007/05/25/string-concatenation-in-sql-server/

|||

There are several different ways to handle this, depending upon whether or not you are storing NULL values or empty strings.

Here is a solution if you are storing NULL values:

Code Snippet

SET NOCOUNT ON

DECLARE @.MyContacts table
( RowID int IDENTITY,
Title varchar(10),
FirstName varchar(20),
MiddleName varchar(20),
LastName varchar(20)
)

INSERT INTO @.MyContacts VALUES ( 'Dr', 'William', 'R', 'Smith' )
INSERT INTO @.MyContacts VALUES ( NULL, 'Bob', NULL, 'Jones' )
INSERT INTO @.MyContacts VALUES ( NULL, 'Mary', 'Anne', 'Williams' )
INSERT INTO @.MyContacts VALUES ( 'Judge', NULL, NULL, 'Wapner' )
INSERT INTO @.MyContacts VALUES ( 'Major', 'Minor', NULL, 'Major' )
INSERT INTO @.MyContacts VALUES ( NULL, 'R', 'P', 'Adams' )

SELECT
PersonsName = isnull((Title + ' '), '' ) +
isnull((FirstName + ' '), '') +
isnull((MiddleName + ' '), '') +
isnull((LastName), '' )
FROM @.MyContacts

PersonsName
-
Dr William R Smith
Bob Jones
Mary Anne Williams
Judge Wapner
Major Minor Major
R P Adams

If you are storing empty strings, you could add a nullif() function around each Field, e.g., isnull((nullif(Title,'') + ' '), '').

|||

Thanks for the replies.

Arnie, your solutions works perfectly, thanks for taking the time.

Regards

Melt