Null & String Ops Again

[BlueDragon]

I don't know enough math to demonstrate that any numerical operation
with a null should yield a null; although I would guess that it's true.
I just don't buy it, however, when dealing with strings and nulls. In a
simple table with first, middle and last name columns, I would infer
that a null value in the middle name column means the HR person forgot
to ask. A zero length string, however, tells me HR did ask and there is
no middle name. Regardless of whether HR asked, when I concatenate the
three fields, I can't think of a sound reason why I souldn't get the
first and last names.
Having now started a flame war, I actually have a question: How do I
set the default for any given database or table so that concat null
yields null is permanently off? I have tried exec sp_dboption
'myDB','concat null yields null',false (and many variations on
delimiting the parameters) but it doesn't do jack. I can use set
concat_null_yie lds_null off but that only lasts for the immediate
session.
I've tried to follow the thread through the BOL but I'm left scratching
my head on how to accomplish this.
Thanks.
Randy

[christopher.secord]

Here's how I do it. Hope this helps:

select
FirstName
+ ' '
+ case when MiddleName is NULL then '' else MiddleName+' ' end
+ LastName

Note: In this example, only the MiddleName field might NULL values.

[Erland Sommarskog]

BlueDragon (bl***********@ yahoo.com) writes:

I don't know enough math to demonstrate that any numerical operation
with a null should yield a null; although I would guess that it's true.
I just don't buy it, however, when dealing with strings and nulls. In a
simple table with first, middle and last name columns, I would infer
that a null value in the middle name column means the HR person forgot
to ask. A zero length string, however, tells me HR did ask and there is
no middle name. Regardless of whether HR asked, when I concatenate the
three fields, I can't think of a sound reason why I souldn't get the
first and last names.
SQL Server can double-guess you. But you can tell SQL Server what you
want:

SELECT firstname + colaesce(middle name, '') + lastname ...

Or define middlename as NOT NULL with '' as the default value.
Having now started a flame war, I actually have a question: How do I
set the default for any given database or table so that concat null
yields null is permanently off? I have tried exec sp_dboption
'myDB','concat null yields null',false (and many variations on
delimiting the parameters) but it doesn't do jack. I can use set
concat_null_yie lds_null off but that only lasts for the immediate
session.

The answer is that you can only do this by session. The database options
are mainly for legacy client libraries that does not set
CONCAT_NULL_YIE LDS_NULL on by default when they connect as modern
libraries do.

I would strongly recommend you to leave this setting on, and fix your
coding instead, no matter what you think of it. There are features in
SQL Server that are not available when this setting is off: indexed
views and indexes on computed columns.
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Hugo Kornelis]

On 3 Mar 2005 14:40:18 -0800, BlueDragon wrote:

I don't know enough math to demonstrate that any numerical operation
with a null should yield a null; although I would guess that it's true.
I just don't buy it, however, when dealing with strings and nulls. In a
simple table with first, middle and last name columns, I would infer
that a null value in the middle name column means the HR person forgot
to ask.
Hi Randy,

You can't infer that. Maybe the HR person did ask, but the employee
refused to disclose his/her middle name? Maybe the scrap of paper where
the middle name was noted was mislaid? Maybe the employee is from
another coutry with another way of giving names and therefor the middle
name is not applicable?

The only thing you can infer from NULL is that the data is missing and
therefor unknown. If the reason why the data is missing is relevant to
the application, it should be modelled, either in a seperate column or
by setting up some special codes for common reasons (i.e. N/A for not
applicable).
A zero length string, however, tells me HR did ask and there is
no middle name.
Agree.
Regardless of whether HR asked, when I concatenate the
three fields, I can't think of a sound reason why I souldn't get the
first and last names.

You can't?

Okay, quick test. My first name is Hugo. My last name is Kornelis. Note
that I don't tell you my middle name, nor whether I even have a middle
name. I also don't tell you why I don't disclose any information about
my middle name. Ergo, in SQL terms my middle name is NULL.

Now please reply to this post with my full name. Let's make it a game:
if your answer is wrong, you'll be banned from asking further questions
for the remaining three months. (Keep in mind that the consequences of a
wrong answer in a database application can have much more severe impact
than a three-month Usenet ban!)

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

[BlueDragon]

Hugo:

Now please reply to this post with my full name. Let's make it a game:
if your answer is wrong, you'll be banned from asking further

questions

I don't know about this game but in my real-world application, the
table is a contact list and if Hugo Konelis is all I have to contact
you with, regardless of what your first or middle name might be (my son
goes by his middle name), that's what I'll use. The ANSI SQL way, I
don't get to contact you at all. Let's play a different game: suppose
there's a bomb in your basement... (How did that old movie go? "Let's
play global thermonuclear war.")
Kidding aside, I appreciate your thoughts. All the comments have given
me something to think about.
Thanks.
Randy

[BlueDragon]

>I would strongly recommend you to leave this setting on, and fix your

coding instead, no matter what you think of it.

The sound you hear is the gnashing of teeth. When Erland speaks, I
listen. So, I removed the set concat_null... and programmed around the
nulls. A function I wrote creates a search string by stripping
everything that isn't a number of a letter of the alphabet from the
string fed to it. The relevant portion of the code:
set @mChar = substring(@mStr ing,@i,1)
set @iAsc = Ascii(@mChar)
set @mChar =
(
case
when @iAsc >= 48 And @iAsc <= 57 Then @mChar
when @iAsc >= 65 And @iAsc <= 90 Then @mChar
when @iAsc >= 97 And @iAsc <= 122 Then @mChar
else ''
end
)
set @msTemp = @msTemp + @mChar

When the else statement was: else null, the function worked fine. When
I changed it to its present form with the zero length string, all of a
sudden I started getting spaces in my search string. There's a place in
the BOL that vaugely (very vaugely) suggests that SQL will return
something more than a zero length string when handed a zero length
string:

The empty string ('') is evaluated as a single space:

"SELECT 'abc' + '' + 'def'

Here is the result set:
-------
abcdef

(1 row(s) affected)

Note Whether an empty string ('') is interpreted as a single blank
character or as an empty character is determined by the compatibility
level setting of sp_dbcmptlevel. For this example, if sp_dbcmptlevel is
65, empty literals are treated as a single blank."

Note, however, that the example does not, in fact, treat the '' as a
single space. Also, in the note, what's a blank character and how is it
different from an empty character? Do they mean a space? I guess they
must because that's what I'm getting. It was a whole lot more straight
forward when concating a string with a null yielded the string.
Oh well.

[Erland Sommarskog]

BlueDragon (bl***********@ yahoo.com) writes:

The sound you hear is the gnashing of teeth. When Erland speaks, I
listen.
I hardly dare to say that the system I work with runs with CONCAT_NULL
and all the other ANSI options off. (The reason is legacy. Our system
have a history since 1992.)
So, I removed the set concat_null... and programmed around the
nulls. A function I wrote creates a search string by stripping
everything that isn't a number of a letter of the alphabet from the
string fed to it. The relevant portion of the code:
set @mChar = substring(@mStr ing,@i,1)
set @iAsc = Ascii(@mChar)
set @mChar =
(
case
when @iAsc >= 48 And @iAsc <= 57 Then @mChar
when @iAsc >= 65 And @iAsc <= 90 Then @mChar
when @iAsc >= 97 And @iAsc <= 122 Then @mChar
else ''
end
)
set @msTemp = @msTemp + @mChar

When the else statement was: else null, the function worked fine. When
I changed it to its present form with the zero length string, all of a
sudden I started getting spaces in my search string.
So what type is @mChar? Judging from the description it sounds as if
it's char(1). That's a fixed-length string, so it will be space-
padded. Change to varchar(1). Or use ltrim.
There's a place in the BOL that vaugely (very vaugely) suggests that SQL
will return something more than a zero length string when handed a zero
length string:
...

Note Whether an empty string ('') is interpreted as a single blank
character or as an empty character is determined by the compatibility
level setting of sp_dbcmptlevel. For this example, if sp_dbcmptlevel is
65, empty literals are treated as a single blank."
This note refers to the compatibility mode for SQL 6.5 which could
not distinguish between empty strings and NULL. NULL for character
values was simply represented as a string with length 0. On 6.5 the
example you quoted does indeed yield "abc def".
Also, in the note, what's a blank character and how is it
different from an empty character?

A blank character is a space. "Empty character" should have been "empty
string".

--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Hugo Kornelis]

On 4 Mar 2005 07:19:16 -0800, BlueDragon wrote:

Hugo:

Now please reply to this post with my full name. Let's make it a game:
if your answer is wrong, you'll be banned from asking furtherquestions

I don't know about this game but in my real-world application

(snip)

Hi Randy,

There's the catch - you know your real-world application, you know that
in this case, the best way to treat an unknowm middle name is to simply
pretend it's not there - therefor, you can use COALESCE to replace NULL
with an empty string.

SQL Server should not make guesses. If you want it to treat NULL as an
empty string,it's easy enough to tell it to. If you don't tell it to, it
should not guess that you might want it to do so anyway.

(my son
goes by his middle name)

Funny you mention that - because, in all honesty, Hugo is in fact my
middle name. My first name is the same as my grandfather's - he died
before I got a chance to really know him, but I do know that I don't
like his name <g>.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)