I have a blob field where i need to get specific strings from the field.
The data in the field looks like this (bolded the info i am trying to capture)
{\rtf1\ansi\deff0\uc1\ansicpg1252\deftab720{\fonttbl{\f0\fnil\fcharset1 Lucida Console;}{\f1\fnil\fcharset1 Microsoft Sans Serif;}{\f2\fnil\fcharset1 Segoe UI;}{\f3\fnil\fcharset2 Wingdings;}{\f4\fnil\fcharset2 Symbol;}}{\colortbl\red0\green0\blue0;\red255\green0\blue0;\red0\green128\blue0;\red0\green0\blue255;\red255\green255\blue0;\red255\green0\blue255;\red128\green0\blue128;\red128\green0\blue0;\red0\green255\blue0;\red0\green255\blue255;\red0\green128\blue128;\red0\green0\blue128;\red255\green255\blue255;\red192\green192\blue192;\red128\green128\blue128;\red0\green0\blue0;}\wpprheadfoot1\paperw12240\paperh15840\margl1880\margr1880\margt1440\margb1440\headery720\footery720\endnhere\sectdefaultcl{*\generator WPTools_6.060-PRM#c;}{\pard\plain\plain\f0\fs20\par \plain\f0\fs20{\field{*\fldinst{MERGEFIELD S_8369e43030774293a8c9b21768fd6875}}{*\wpfldparam{Brief Operative Note'0D '0A}}{\fldrslt{\f1\fs27\cf0\cb0 Brief Operative Note \line\f0\fs20}}}\f0\wpiparam15132390\wphzlinc15132390\wphzlinetw20\par \plain\f2\fs19{\field{*\fldinst{MERGEFIELD C_8fb1bbcd2bac4d43ba63a034a8b5082a}}{*\wpfldparam{Team }}{\fldrslt{\f2\fs22\cf0\cb0\b Team \f2\fs19\b0}}}\par \wpparid0\plain\f2\fs23 > Surgeon(s): \tab\tab name of surgeon MD.\par \wpparid0\plain\f2\fs19{\field{*\fldinst{MERGEFIELD C_7fadbc100eb3406a81d22fdbad00a72e}}{*\wpfldparam{Pre-Op Diagnosis }}{\fldrslt{\f2\fs22\cf0\cb0\b Pre-Op Diagnosis \f2\fs19\b0}}}\par \wpparid0\plain\f2\fs22 Left knee osteoarthritis\par }}
i am using CHARINDEX('Surgeon(s):',BLOBJT_BODY) as SURGEON_START,
to get the surgeon position
i then put that into a temp table
and i end up using a case statement
CASE WHEN SURGEON_START =1191 THEN SUBSTRING(BLOBJT_BODY,1191,80)
WHEN SURGEON_START =1360 THEN SUBSTRING(BLOBJT_BODY,1360,80) END AS SURG,
what i would like to do is to be able to use substring for position created by the
CHARINDEX('Surgeon(s):',BLOBJT_BODY) as SURGEON_START
some results are 1360, 1191, 1200, 2. the position is not always consistent and may change when new docs are created.
that then goes into a temp table and then i use
SUBSTRING(SURG, 23, CHARINDEX('\par', SURG+'\par',0)) AS SURGEON,
which ends up gettting extremly close to what i am looking for. these are my results
Surgeon(s): \tab\tab SURGEON 1 MD, SURGEON2 MD, SURGEON 3
I can not post my full stored proc because of security at my hospital. please try to help with the information i provided.