ISNULL function does not allow concatenating CHAR's in SQL 2008R2 or earlier

Question

Post reply

ISNULL function does not allow concatenating CHAR's in SQL 2008R2 or earlier

tskelley

SSCommitted

Points: 1870
More actions
July 29, 2016 at 2:11 pm

#328523

The query looks similar to:
SELECT STUFF((SELECT ISNULL(field + CHAR(10) + CHAR(10),'')
FROM
FOR XML PATH('')),1,0,'')
It is being used as part of a CROSS APPLY to bring back a list of fields each on a new line. The code strangely enough works fine in SQL 2012 and higher. I also tried replacing ISNULL with COALESCE, but this still did not work for older versions of SQL. If we just use:
SELECT STUFF((SELECT ISNULL(field,'') + CHAR(10) + CHAR(10)
then the characters (line feed symbols) will show for any null fields in the report.
I'm a little stumped here, and thinking I may need to resort to a CASE statement to pull this off. Any ideas are greatly appreciated!
Thank you,
Tom

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

It's working as designed. If you try to concatenate a string with a NULL value, the result will be a NULL value.

Check the following example:

SELECT field,

field + 'x',

ISNULL(field + 'x',''),

ISNULL(field,'') + 'x'

FROM (VALUES ('Test'), (''), (NULL))x(field);

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

tskelley SSCommitted Points: 1870 More actions · Answer 2

Thanks for the quick reply. My problem is not so much with the functionality of ISNULL with a concatenated field. My problem is the syntax I need to use is not supported in SQL 2008 R2 or below. I get the following error messages when trying to use in SQL 2008 R2 and below:

Msg 102, Level 15, State 1, Procedure VIEW_XXXXXXXX, Line 74775

Incorrect syntax near ' '.

Msg 102, Level 15, State 1, Procedure VIEW_XXXXXXXX, Line 74806

Incorrect syntax near ' '.

Msg 102, Level 15, State 1, Procedure VIEW_XXXXXXXX, Line 74830

Incorrect syntax near ' '.

Msg 102, Level 15, State 1, Procedure VIEW_XXXXXXXX, Line 74837

Incorrect syntax near ' '.

Msg 102, Level 15, State 1, Procedure VIEW_XXXXXXXX, Line 74844

Incorrect syntax near ' '.

Msg 102, Level 15, State 1, Procedure VIEW_XXXXXXXX, Line 74851

Incorrect syntax near ' '.

Sean Lange SSC Guru Points: 286590 More actions · Answer 3

tskelley (7/29/2016)
Thanks for the quick reply. My problem is not so much with the functionality of ISNULL with a concatenated field. My problem is the syntax I need to use is not supported in SQL 2008 R2 or below. I get the following error messages when trying to use in SQL 2008 R2 and below:
Msg 102, Level 15, State 1, Procedure VIEW_XXXXXXXX, Line 74775
Incorrect syntax near ' '.
Msg 102, Level 15, State 1, Procedure VIEW_XXXXXXXX, Line 74806
Incorrect syntax near ' '.
Msg 102, Level 15, State 1, Procedure VIEW_XXXXXXXX, Line 74830
Incorrect syntax near ' '.
Msg 102, Level 15, State 1, Procedure VIEW_XXXXXXXX, Line 74837
Incorrect syntax near ' '.
Msg 102, Level 15, State 1, Procedure VIEW_XXXXXXXX, Line 74844
Incorrect syntax near ' '.
Msg 102, Level 15, State 1, Procedure VIEW_XXXXXXXX, Line 74851
Incorrect syntax near ' '.

Then perhaps you could share the whole query you are working with?

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Lowell SSC Guru Points: 323518 More actions · Answer 4

wow Line 74851

thats a huge proc to debug.

at that size, it must be doing a zillion things, that's rough.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

tskelley SSCommitted Points: 1870 More actions · Answer 5

It's actually the SQL from a view being created that is part of a large deployment script.

Luis Cazares SSC Guru Points: 183706 More actions · Answer 6

tskelley (7/31/2016)
Thank you Inspector Holmes. It's actually the SQL from a view being created that is part of a large deployment script.

You know? Sarcasm isn't a good idea when you're asking help for free to volunteers with no pay.[/strike]

Also, without details there's no way we can know what's going on there. The syntax that you posted is supported by SQL Server 2008. If the code is part of a view, post the view definition (at least). You could also read the articles linked in my signature to get better help.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

tskelley SSCommitted Points: 1870 More actions · Answer 7

Yes, and thank for your pointing out the sarcasm. I have removed it from my reply. I was a little frustrated on this one, and found the comments were on the periphery of the issue.

And yes, I continued to battle this over the weekend and found out there is an issue with some kind of "hidden" character(s) causing the view not to format correctly for older versions of SQL (2008R2 and below were affected). I had never run into an issue like this before, and the only difference in code was the ISNULL without and then with the CHAR(10) being included, but as I mentioned this was not the issue.

Thank you all for your help,

Tom

Luis Cazares SSC Guru Points: 183706 More actions · Answer 8

tskelley (8/1/2016)
Yes, and thank for your pointing out the sarcasm. I have removed it from my reply. I was a little frustrated on this one, and found the comments were on the periphery of the issue.
And yes, I continued to battle this over the weekend and found out there is an issue with some kind of "hidden" character(s) causing the view not to format correctly for older versions of SQL (2008R2 and below were affected). I had never run into an issue like this before, and the only difference in code was the ISNULL without and then with the CHAR(10) being included, but as I mentioned this was not the issue.
Thank you all for your help,
Tom

It's good to know that you found the solution to your problem.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 9

tskelley (8/1/2016)
Yes, and thank for your pointing out the sarcasm. I have removed it from my reply. I was a little frustrated on this one, and found the comments were on the periphery of the issue.
And yes, I continued to battle this over the weekend and found out there is an issue with some kind of "hidden" character(s) causing the view not to format correctly for older versions of SQL (2008R2 and below were affected). I had never run into an issue like this before, and the only difference in code was the ISNULL without and then with the CHAR(10) being included, but as I mentioned this was not the issue.
Thank you all for your help,
Tom

Tom, thank you for posting that you had solved your problem and explaining what the issue turned out to be. It's good to have that for closure. We've all been through times when we were supremely frustrated over problem issues and the frustration is compounded when its difficult to supply the details that others are asking for. But please understand that everyone answering questions here is doing so on their own time out of the goodness of their hearts. No one (except Steve) is getting paid to do it. No one has a service-level agreement with the people posting questions, but they really do want to help. We're all glad you found your answer. Best of luck in the future.

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills