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 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy