June 2, 2002 at 8:48 am
Can anyone help with parsing names from a muti-value field, each one separated with an embeded carrige return line feed.I need to exract each name together with the identity No and insert it into a different table.
As an Example which doesn't work:
USE smmMedical
GO
CREATE PROCEDURE sp_sort_doctors
AS
/* Local variables to hold query results*/
DECLARE @idDoc int,
@BM nvarchar(1500),
@Pos nvarchar(1500)
/* Declare the Cursor for the Query */
DECLARE Doctor_curs CURSOR FOR
SELECT dbo.smmDirInfo.idDocument,
dbo.smmDirInfo.BM,
dbo.smmDirInfo.Pos
FROM dbo.smmDirInfo INNER JOIN
dbo.smmDocuments ON dbo.smmDirInfo.idDocument = dbo.smmDocuments.id INNER JOIN
dbo.smmHeadings ON dbo.smmDocuments.idHeading = dbo.smmHeadings.id INNER JOIN
dbo.smmText ON dbo.smmDirInfo.idDocument = dbo.smmText.idDocument INNER JOIN
dbo.smmSource ON dbo.smmDocuments.idSource = dbo.smmSource.id INNER JOIN
dbo.smmSource smmSource_1 ON dbo.smmSource.idParent = smmSource_1.id
WHERE (dbo.smmHeadings.id = 49)
BEGIN
/* Open cursor and fetch first row of results */
OPEN Doctor_curs
FETCH Doctor_curs INTO @idDoc, @BM, @Pos
/* If no rows, return immediately */
If (@@FETCH_STATUS = 2)
BEGIN
close Doctor_curs
return
END
/* Loop through each row of the query results */
WHILE (@@FETCH_STATUS <> -1)
BEGIN
/* Check for Doctors */
If (@BM LIKE 'Dr' )
INSERT INTO Doctors
VALUES( @idDoc, @BM)
/* Check for Practice Managers */
Else If (@BM NOT LIKE 'Dr' )
INSERT INTO PracticeManagers
VALUES( @idDoc,@BM, @Pos)
END
/* Done with results; close cursor and return */
CLOSE Doctor_curs
Deallocate Doctor_curs
END
June 2, 2002 at 1:34 pm
check out my article on Charindex. I'm sure that will help you. Search for the ascii of the carriage return.
Steve Jones
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply