JackW9653
asked on
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 ('^',Alert 1)+1,LEN(A lert1))+ '; ' +
SUBSTRING(Alert2,CHARINDEX ('^',Alert 2)+1,LEN(A lert2)) + '; ' +
SUBSTRING(Alert3,CHARINDEX ('^',Alert 3)+1,LEN(A lert3)) + '; ' +
SUBSTRING(Alert4,CHARINDEX ('^',Alert 4)+1,LEN(A lert4)) + '; ' +
SUBSTRING(Alert5,CHARINDEX ('^',Alert 5)+1,LEN(A lert5)) + '; ' +
SUBSTRING(Alert6,CHARINDEX ('^',Alert 6)+1,LEN(A lert6)) + '; ' +
SUBSTRING(Alert7,CHARINDEX ('^',Alert 7)+1,LEN(A lert7)) + '; ' +
SUBSTRING(Alert8,CHARINDEX ('^',Alert 8)+1,LEN(A lert8)) + '; ' +
SUBSTRING(Alert9,CHARINDEX ('^',Alert 9)+1,LEN(A lert9)) + '; ' +
SUBSTRING(Alert10,CHARINDE X('^',Aler t10)+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
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
SUBSTRING(Alert2,CHARINDEX
SUBSTRING(Alert3,CHARINDEX
SUBSTRING(Alert4,CHARINDEX
SUBSTRING(Alert5,CHARINDEX
SUBSTRING(Alert6,CHARINDEX
SUBSTRING(Alert7,CHARINDEX
SUBSTRING(Alert8,CHARINDEX
SUBSTRING(Alert9,CHARINDEX
SUBSTRING(Alert10,CHARINDE
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER