473,320 Members | 1,988 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Null & String Ops Again

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_yields_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

Jul 23 '05 #1
7 5526
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.

Jul 23 '05 #2
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(middlename, '') + 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_yields_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_YIELDS_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****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #3
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)
Jul 23 '05 #4
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

Jul 23 '05 #5
>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(@mString,@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.

Jul 23 '05 #6
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(@mString,@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****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #7
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)
Jul 23 '05 #8

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

6
by: mali_djuro | last post by:
Hi all, i used JDOM to create XML file. first, i get data from database and create Document object. in some data i have quotas, so it makes replacment in content of elements. for example: if i...
19
by: Gav | last post by:
Hi, At the moment i am checking that all the fields have been filled out, at the moment i am using the following... if firstname = "" and surname = "" and address1 = "" and town = "" and county...
6
by: scott | last post by:
i've come across a real head-hurter. I'm looping through a recordset and response.writing it's rows out with no problem except 1 field. The field type is varchar and contains words like meeting,...
4
by: webdev | last post by:
lo all, some of the questions i'll ask below have most certainly been discussed already, i just hope someone's kind enough to answer them again to help me out.. so i started a python 2.3...
12
by: ~~~ .NET Ed ~~~ | last post by:
Hi, I have a standalone XML file (with the appropriate xml document header) that works fine when I load it using XmlDocument. I can have child elements like this without problems: ...
4
by: MadCrazyNewbie | last post by:
Hey Group, Just another quick question: I have the following bit of code: Private Sub cboPasswordListsDepartment_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs)...
7
by: c#2006user | last post by:
Hi everyone. i know there is a lot of code and ive reduced it as much as i could, its a bit much to ask but i am really stuck! i've been at this for a week! this is converted from vb to c# and...
3
by: Ganesh Palaniappan | last post by:
We're getting following exception for the below piece of code. We're wondering how it is possible since we're having a null check for objGraphics and strokePen... Exception:...
12
by: InvalidLastName | last post by:
We have been used XslTransform. .NET 1.1, for transform XML document, Dataset with xsl to HTML. Some of these html contents contain javascript and links. For example: // javascript if (a &gt; b)...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.