Concatenating strings with possible NULLs

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Concatenating strings with possible NULLs

Author

Topic

jackdewey
Starting Member

7 Posts

Posted - 2006-08-04 : 14:46:01

Hi,

My company's database server was recently upgraded to SQL Server 2005. One of my stored procedures is no longer working in this new environment. I have searched a bit through the help but have not found anything useful so I thought this forum might be able to help as it did before.

Quite simply, I am doing a select statement where one of the fields just adds a whole bunch of characters fields together. Some of these characters fields have a NULL value.

"select Field1 + Field2 + Field3 as ConcatField"

In SQL Server 7.0, the NULLs just wouldn't take up any space and I would end up with one string that would be the concatenation of all strings that had values. However, in SQL Server 2005, as soon as there is a NULL in that list of fields, I get a NULL as the output.

Is there some sort of SQL Server configuration setting that I might need to change for this?

Thanks for any help you can provide,

Yannick

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-08-04 : 14:52:46

May be u might use ISNULL function

Srinika

jackdewey
Starting Member

7 Posts

Posted - 2006-08-04 : 14:59:24

Thanks for the suggestion, I did not know this function. However, after trying it, I have error messages showing up. Here is my full statement so you can see what I am trying to do:

select ISNULL(Product1Act, "") + ISNULL(Product5Act, "") as maint, accountid from (

SELECT Product1Act = CASE WHEN VAPS_ACTIVE = 'T' THEN 'V ' ELSE NULL END,
Product2Act = CASE WHEN HELSIM_ACTIVE = 'T' THEN 'H ' ELSE NULL END,
Product3Act = CASE WHEN STAGE_ACTIVE = 'T' THEN 'S ' ELSE NULL END,
Product4Act = CASE WHEN FLSIM_ACTIVE = 'T' THEN 'F ' ELSE NULL END,
Product5Act = CASE WHEN QCG_ACTIVE = 'T' THEN 'Q ' ELSE NULL END,
Product6Act = CASE WHEN AI_ACTIVE = 'T' THEN 'AI ' ELSE NULL END,
Product7Act = CASE WHEN SIM_ACTIVE = 'T' THEN 'SB ' ELSE NULL END,
Product8Act = CASE WHEN JLOOX_ACTIVE = 'T' THEN 'J ' ELSE NULL END,
Product9Act = CASE WHEN DWB_ACTIVE = 'T' THEN 'DWB ' ELSE NULL END,
Product10Act = CASE WHEN VAPSXT_ACTIVE = 'T' THEN 'VXT ' ELSE NULL END,
a1.accountid
FROM sysdba.account a1, sysdba.account2 a2
where a1.accountid = a2.accountid ) Crunch
order by maint desc

The error that I am getting is:

An object or column name is missing or empty. For SELECT INTO statements, verify each column has a name. For other statements, look for empty alias names. Aliases defined as "" or [] are not allowed. Add a name or single space as the alias name.

Any ideas?

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-08-04 : 15:04:53

U can't use double quotes, instead use single quotes

ISNULL(Product1Act, "") --> ISNULL(Product1Act, '')

Srinika

jackdewey
Starting Member

7 Posts

Posted - 2006-08-04 : 15:06:58

DOH! I always get caught with these silly quotes :) Thanks!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-08-05 : 05:52:47

Double quote is for front end application

Madhivanan

Failing to plan is Planning to fail