Solved

concat null yields null

Posted on 2001-09-10
11
1,116 Views
Last Modified: 2007-11-27
The statement
'select field1 + ' ' + field2 + ' ' + field3 from ...'
gives me null output even that "concat null yields null" is set to false.
Is there any other parameter that need to be set/unset to retrieve string instead of null when one of substrings in null?
Cheers
0
Comment
Question by:PeterZG
  • 4
  • 3
  • 3
  • +1
11 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 50 total points
ID: 6471393
in SQL Server, i use this, avoiding to base myself on the settings:

select COALESCE(field1, '') + ' ' + COALESCE( field2,'') + ' ' + COALESCE(field3,'') from ...

Cheers
0
 
LVL 1

Author Comment

by:PeterZG
ID: 6471448
I was thinking about it, but the problem with this solution is that I'll need to change several views and stored procedures. There was a change to the existing system and one of the fields is now nullable. I don't want to go through a manual ammending excercise...
0
 
LVL 6

Expert Comment

by:jchopde
ID: 6471502
In that case, you could create a view with "isnull" in a SELECT. Existing logic will work then. You may need to rename the "real" table and create the view with the original  table's name to maintain the "no code change" goal, which might open up some other can of worms... HTH.
0
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
LVL 1

Author Comment

by:PeterZG
ID: 6471523
Just wonder why "concat null yields null" doesn't work?
I'm using M$SQL7 SP3
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 6471635
BOL:

concat null yields null
When true, if one of the operands in a concatenation operation is NULL, the result of the operation is NULL. For example, concatenating the character string ?This is? and NULL results in the value NULL, rather than the value ?This is?.
When false, concatenating a null value with a character string yields the character string as the result; the null value is treated as an empty character string. By default, concat null yields null is false.

Session-level settings (set using the SET statement) override the default database setting for concat null yields null. By default, ODBC and OLE DB clients issue a SET statement setting concat null yields null to true for the session when connecting to SQL Server. For more information, see SET CONCAT_NULL_YIELDS_NULL.

The status of this option can be determined by examining the IsNullConcat property of the DATABASEPROPERTY function.

SET CONCAT_NULL_YIELDS_NULL {ON | OFF}

Remarks
When SET CONCAT_NULL_YIELDS_NULL  is ON, concatenating a null value with a string yields a NULL result. For example, SELECT ?abc? + NULL yields NULL. When SET CONCAT_NULL_YIELDS_NULL is OFF, concatenating a null value with a string yields the string itself (the null value is treated as an empty string). For example, SELECT ?abc? + NULL yields abc.

If not specified, the setting of the concat null yields null database option applies.


--------------------------------------------------------------------------------

Note SET CONCAT_NULL_YIELDS_NULL is the same setting as the concat null yields null setting of sp_dboption.


--------------------------------------------------------------------------------

The setting of SET CONCAT_NULL_YIELDS_NULL  is set at execute or run time and not at parse time.


0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 6471637
ooops... copy/paste changes ' into ?  , sorry for that
0
 
LVL 1

Author Comment

by:PeterZG
ID: 6471697
select databaseproperty('MyDB', 'IsNullConcat') gives 0, so I shouldn't get null value, but I'm getting it...
What am I missing here?
0
 
LVL 6

Expert Comment

by:jchopde
ID: 6471698
Folks, this does not work as advertised for SQL 7.0 :-(
You can make it work by setting your database compatibility level to 65. So if you run
sp_dbcmptlevel '<<dbname>>', 65
sp_dboption '<<dbname>>','concat null yields null','true'

and do your query, it will work ! Docs say the EXACT opposite ! Don't know if you have the luxury of setting the compatibility level to 65. HTH.
0
 
LVL 6

Expert Comment

by:jchopde
ID: 6471782
Well, I jumped the gun looks like. It DOES work as advertised but as angelIII and BOL point out, ODBC and SQL Query Analyzer will turn this ON by default so you need to explicitly turn the behavior OFF if you are using either of these connection mechanisms. Pete, I guess its code change or compatibility level ... take your pick :-)
0
 
LVL 6

Expert Comment

by:curtis591
ID: 6471983
Sounds a bit tricky with the settings to me.  We just use the isnull function in sql.
ex:
isnull(field1,'') + isnull(field2,'') + isnull(field3,'')
0
 
LVL 1

Author Comment

by:PeterZG
ID: 7163695
Guys,
sorry for leaving that question opened so long.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Row insertion failed. Array 5 48
TSQL XML Namespaces 7 24
MS SQL Server select from Sub Table 14 26
Need to find substring in SQL 5 14
Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question