Blank SELECT because of NULL?

Blank SELECT because of NULL?

Post by Peterson, Mar » Thu, 08 Jun 2000 04:00:00



=============================
When I run the following query I get this:

select firstname,middlename,lastname from intranet..employees where
lastname='le-nguyen'

firstname                                middlename
lastname
---------------------------------------- ------------------------------
My                                       NULL
Le-Nguyen

(1 row(s) affected)
=============================
A modified version of the query I get this:

select FirstName + ' ' + LastName from intranet..employees where
lastname='le-nguyen'

----------------------------------------------------------------------------
-----
My Le-Nguyen

(1 row(s) affected)
=============================
But when I run this query I get NULL (added the 'MiddleName' field which
its' value is NULL):

select FirstName + ' ' + MiddleName + ' ' + LastName from
intranet..employees where lastname='le-nguyen'
=============================
I want to be able to run the 3rd query and at least get the FirstName and
LastName fields to show up.  What is wrong with this query?

 
 
 

Blank SELECT because of NULL?

Post by c.. » Thu, 08 Jun 2000 04:00:00


Mark,

When you concatenate something to a null, the whole string becomes
null.  Add the ISNULL parameter and you should be all set.

(See BOL if you need an explanation of what this does)

Example:
select FirstName + ' ' + ISNULL(MiddleName, '') + ' ' + LastName from
intranet..employees where lastname='le-nguyen'

The downfall of this is if there is no middlename, you will have two
spaces between the first and last names, but you could eliminate that
by using a CASE statement.



Quote:> =============================
> When I run the following query I get this:

> select firstname,middlename,lastname from intranet..employees where
> lastname='le-nguyen'

> firstname                                middlename
> lastname
> ---------------------------------------- -----------------------------
-
> My                                       NULL
> Le-Nguyen

> (1 row(s) affected)
> =============================
> A modified version of the query I get this:

> select FirstName + ' ' + LastName from intranet..employees where
> lastname='le-nguyen'

> ----------------------------------------------------------------------
------
> -----
> My Le-Nguyen

> (1 row(s) affected)
> =============================
> But when I run this query I get NULL (added the 'MiddleName' field
which
> its' value is NULL):

> select FirstName + ' ' + MiddleName + ' ' + LastName from
> intranet..employees where lastname='le-nguyen'
> =============================
> I want to be able to run the 3rd query and at least get the FirstName
and
> LastName fields to show up.  What is wrong with this query?

Sent via Deja.com http://www.deja.com/
Before you buy.

 
 
 

Blank SELECT because of NULL?

Post by dialstar » Fri, 09 Jun 2000 04:00:00


try

select firstname + isnull( ' ' + middlename, '' ) + ' ' + lastname

Tony


Quote:> =============================
> When I run the following query I get this:

> select firstname,middlename,lastname from intranet..employees where
> lastname='le-nguyen'

> firstname                                middlename
> lastname
> ---------------------------------------- ------------------------------
> My                                       NULL
> Le-Nguyen

> (1 row(s) affected)
> =============================
> A modified version of the query I get this:

> select FirstName + ' ' + LastName from intranet..employees where
> lastname='le-nguyen'

> --------------------------------------------------------------------------
--
> -----
> My Le-Nguyen

> (1 row(s) affected)
> =============================
> But when I run this query I get NULL (added the 'MiddleName' field which
> its' value is NULL):

> select FirstName + ' ' + MiddleName + ' ' + LastName from
> intranet..employees where lastname='le-nguyen'
> =============================
> I want to be able to run the 3rd query and at least get the FirstName and
> LastName fields to show up.  What is wrong with this query?

 
 
 

Blank SELECT because of NULL?

Post by Trey Johnso » Fri, 09 Jun 2000 04:00:00


You can also set the option -

SET ANSI_NULLS OFF

Trey


Quote:> =============================
> When I run the following query I get this:

> select firstname,middlename,lastname from intranet..employees where
> lastname='le-nguyen'

> firstname                                middlename
> lastname
> ---------------------------------------- ------------------------------
> My                                       NULL
> Le-Nguyen

> (1 row(s) affected)
> =============================
> A modified version of the query I get this:

> select FirstName + ' ' + LastName from intranet..employees where
> lastname='le-nguyen'

> --------------------------------------------------------------------------
--
> -----
> My Le-Nguyen

> (1 row(s) affected)
> =============================
> But when I run this query I get NULL (added the 'MiddleName' field which
> its' value is NULL):

> select FirstName + ' ' + MiddleName + ' ' + LastName from
> intranet..employees where lastname='le-nguyen'
> =============================
> I want to be able to run the 3rd query and at least get the FirstName and
> LastName fields to show up.  What is wrong with this query?

 
 
 

Blank SELECT because of NULL?

Post by Kalen Delane » Sat, 10 Jun 2000 04:00:00


Trey

ANSI_NULLS will not affect this behavior. ANSI_NULLS only controls whether
testing whether a column = NULL is the same as testing whether a column IS
NULL.

This behavior Mark is describing is controlled by the set option
CONCAT_NULL_YIELDS_NULL, which is new in SQL Server 7.

HTH

--
Kalen Delaney
MCSE, SQL Server MCT, MVP
www.InsideSQLServer.com
Feed Someone for Free Today:
     www.TheHungerSite.com


> You can also set the option -

> SET ANSI_NULLS OFF

> Trey



> > =============================
> > When I run the following query I get this:

> > select firstname,middlename,lastname from intranet..employees where
> > lastname='le-nguyen'

> > firstname                                middlename
> > lastname
> > ---------------------------------------- ------------------------------
> > My                                       NULL
> > Le-Nguyen

> > (1 row(s) affected)
> > =============================
> > A modified version of the query I get this:

> > select FirstName + ' ' + LastName from intranet..employees where
> > lastname='le-nguyen'

> --------------------------------------------------------------------------
> --
> > -----
> > My Le-Nguyen

> > (1 row(s) affected)
> > =============================
> > But when I run this query I get NULL (added the 'MiddleName' field which
> > its' value is NULL):

> > select FirstName + ' ' + MiddleName + ' ' + LastName from
> > intranet..employees where lastname='le-nguyen'
> > =============================
> > I want to be able to run the 3rd query and at least get the FirstName
and
> > LastName fields to show up.  What is wrong with this query?

 
 
 

1. nulls versus blanks

We are using SQL server 2000 with a VB 6 client. We are having a problem
with date columns that allow nulls.When the user enters a value in a date
column, deletes the value, and clicks the update button, we receive an error
message saying "Operation cancelled. Record was not added." we believe this
is due to a difference between blank values in a text box versus null values
in date column. Any suggestions?

Thanks in advance,
David

2. US-VA: Reston-Oracle DBA

3. Substitute Blank for Null Text Column

4. Retrieving XML Data from a URL

5. nulls versus blanks

6. Migrating from Oracle7.3 to Oracle8

7. blank vs. null

8. US-PA-PHL PROGRESS Consultant Needed ASAP

9. BCP and blank=null

10. Null or blank values in a field in a DBGrid

11. Null / Blank / Empty String Problems

12. Null instead of blank

13. Provide Blank/Null Date Entry to Access Database