Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 386
  • Last Modified:

Continue until NULL in SQL Server

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 STUFF(
(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)) + '; ' +
 SUBSTRING(Alert10,CHARINDEX('^',Alert10)+1,LEN(Alert10))
    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.

Thanks,

JackW9653
0
JackW9653
Asked:
JackW9653
1 Solution
 
BeartlaoiCommented:
Use an ISNULL for each expression:
SELECT STUFF(
(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)),'') + '; ' + 
 ISNULL(SUBSTRING(Alert10,CHARINDEX('^',Alert10)+1,LEN(Alert10)),'')
    FROM tblPatientData
      WHERE CaseNumber = '10163001'
    FOR XML PATH ('')),1,1,'')

Open in new window

0
 
JackW9653Author Commented:
That works, thanks for your quick reply.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now