December 17, 2008 at 1:05 pm
I'm writing a stored procedure that will pull in patient data. One item that I need is the Findings from chest xrays. The text from that document is one large text field. I don't want the whole document; just whatever is documented after the word "Findings:" That word could be at ANY point in the document - it could be 100 characters into the string; it could be 1000 characters into the string. I want it to find the word "Findings" in the string, and then give me whatever comes after it. I've been playing with substring and charindex, but haven't gotten anything to work right yet. Please help if you can! Thanks!
December 17, 2008 at 1:40 pm
Something like this?
DECLARE @a TABLE(
ATextvarchar(1000))
INSERT INTO @a(AText)
VALUES('This is some sample text. It is almost ChristmasTime! X-Ray Results look uh... good... (I''m a programmer, not a doctor)')
INSERT INTO @a(AText)
VALUES('This is a second Line, just to put X-Ray (Still Good) in a different place')
SELECT RIGHT(AText,DATALENGTH(AText)-PATINDEX('%X-Ray%',AText)-5)
FROM @a A
This is not likely to be that fast.
If there are issues with this (such as using an actual TEXT field), please refer to the post in my signatuer on how to post table structure / sample data so we can eliminate the variables.
December 17, 2008 at 1:43 pm
Also, if it is actually a TEXT field, just change the RIGHT to a substring
SELECT SUBSTRING(AText,PATINDEX('%X-Ray%',AText)+5,DATALENGTH(AText)-PATINDEX('%X-Ray%',AText)-5)
December 17, 2008 at 1:54 pm
You're too quick for me today, Seth. One important thing to remember is that although charindex and substring work normally with text columns, the LEN function doesn't. The use of DATALENGTH above is the key to making this work.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply