Continue until NULL in SQL Server

Posted on 2013-11-20
Last Modified: 2013-11-20
Hi experts,

I'm using the following to put ';' after each choice in a multi-column query but it's returning NULL even if there are actual results in the first 9 positions but a NULL in Alert10.

(SELECT  '; '+ SUBSTRING(Alert1,CHARINDEX('^',Alert1)+1,LEN(Alert1))+ '; ' +
 SUBSTRING(Alert2,CHARINDEX('^',Alert2)+1,LEN(Alert2)) + '; ' +
 SUBSTRING(Alert3,CHARINDEX('^',Alert3)+1,LEN(Alert3)) + '; ' +
 SUBSTRING(Alert4,CHARINDEX('^',Alert4)+1,LEN(Alert4)) + '; ' +
 SUBSTRING(Alert5,CHARINDEX('^',Alert5)+1,LEN(Alert5)) + '; ' +
 SUBSTRING(Alert6,CHARINDEX('^',Alert6)+1,LEN(Alert6)) + '; ' +
 SUBSTRING(Alert7,CHARINDEX('^',Alert7)+1,LEN(Alert7)) + '; ' +
 SUBSTRING(Alert8,CHARINDEX('^',Alert8)+1,LEN(Alert8)) + '; ' +
 SUBSTRING(Alert9,CHARINDEX('^',Alert9)+1,LEN(Alert9)) + '; ' +
    FROM tblPatientData
      WHERE CaseNumber = '10163001'
    FOR XML PATH ('')),1,1,'')

I need a query that will stop when it hits or ignore the NULL. I've played with COALESCE but it kept blowing up on me because of syntax errors. So I'm turning to you guys.


Question by:JackW9653

Accepted Solution

Beartlaoi earned 500 total points
Comment Utility
Use an ISNULL for each expression:
(SELECT  '; '+ SUBSTRING(Alert1,CHARINDEX('^',Alert1)+1,LEN(Alert1))+ '; ' + 
 ISNULL(SUBSTRING(Alert2,CHARINDEX('^',Alert2)+1,LEN(Alert2)),'') + '; ' +
 ISNULL(SUBSTRING(Alert3,CHARINDEX('^',Alert3)+1,LEN(Alert3)),'') + '; ' +
 ISNULL(SUBSTRING(Alert4,CHARINDEX('^',Alert4)+1,LEN(Alert4)),'') + '; ' + 
 ISNULL(SUBSTRING(Alert5,CHARINDEX('^',Alert5)+1,LEN(Alert5)),'') + '; ' + 
 ISNULL(SUBSTRING(Alert6,CHARINDEX('^',Alert6)+1,LEN(Alert6)),'') + '; ' +
 ISNULL(SUBSTRING(Alert7,CHARINDEX('^',Alert7)+1,LEN(Alert7)),'') + '; ' +
 ISNULL(SUBSTRING(Alert8,CHARINDEX('^',Alert8)+1,LEN(Alert8)),'') + '; ' + 
 ISNULL(SUBSTRING(Alert9,CHARINDEX('^',Alert9)+1,LEN(Alert9)),'') + '; ' + 
    FROM tblPatientData
      WHERE CaseNumber = '10163001'
    FOR XML PATH ('')),1,1,'')

Open in new window


Author Closing Comment

Comment Utility
That works, thanks for your quick reply.

Featured Post

Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.

744 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now