0 Replies Latest reply on Mar 25, 2019 8:54 PM by faulkner's maze

    matching REGEXP_SUBSTR product from another table

    faulkner's maze

      Hello all-

      I am parsing text strings that denote microbiology organisms-so that I may have  a string that is 'Greater than 100,000 col/ml Greater than 100,000 col/ml Proteus mirabilis'

      I use the statement  below and this gets me what I need -  ' Proteus mirabilis' -so far so good-

       

       

      SELECT  regexp_replace('Greater than 100,000 col/ml Proteus mirabilis', '(.*)(ml|ML)(.*)', '\3') as exp1,

           FROM DUAL;

       

       

      Now in table B I have a whole list of organisms that  I need to see if there is a match as some are of interest and others are not-

      I am unclear on how to to use the  EXP above to see if there is a matching organism per se -

       

      But here is the catch-what comes from EXP may or may not match exactly-  

      for example - from above-Proteus mirabilis -now needs to match against 'Extended spectrum beta-lactamase producing Proteus mirabilis' in another table-

      I got the below code to work-

       

       

      INSTR('Extended spectrum beta-lactamase producing Proteus mirabilis', regexp_replace('Greater than 100,000 col/ml Proteus mirabilis', '(.*)(ml|ML)(.*)', '\3'), -3, 1) and this works  fine but....

      but I really need  to do is actually tie  the EXP result from TABLE A to the organism from table B  together-

       

       

      Perhaps as a subquery-a join perhaps. Entire query below-

       

      SELECT  distinct order_results.ORDER_PROC_ID,order_results.PAT_ID, order_results.PAT_ENC_CSN_ID, order_results.RESULT_TIME,

      ORDER_TIME,pat_mrn_id,

      SPECIMN_TAKEN_DATE,SPECIMN_TAKEN_TIME, RESULT_FLAG_C,ABNORMAL_YN,

      RESULTS_COMP_CMT,START_DT as START_CATH_TIME,end_DT as END_CATH_TIME,

      UPPER(REGEXP_SUBSTR(substr(regexp_replace( RESULTS_COMP_CMT, '(.*)(ml|ML)(.*)', '\3'),1,50),'[^(]+',1,1)) as exp2

       

       

          FROM order_results

      INNER JOIN ORDER_PROC ON ORDER_PROC.order_proc_id = order_results.order_proc_id

      LEFT JOIN ORDER_PROC_2  ON ORDER_PROC.order_proc_id=ORDER_PROC_2.order_proc_id

       

      LEFT JOIN ZC_ORDER_CLASS D ON  ORDER_PROC.ORDER_CLASS_C=D.ORDER_CLASS_C

      LEFT JOIN PATIENT T ON order_results.PAT_ID=T.PAT_ID

      inner join foley_test X on T.PAT_MRN_ID=X.PATID and SPECIMN_TAKEN_DATE between trunc(START_DT) and trunc(to_date(END_DT + interval '1' DAY) )

          left join ORDER_RES_COMP_CMT on order_results.order_proc_id =ORDER_RES_COMP_CMT.order_id

       

       

          and proc_code in ('87088N','87088C')

      and order_results.LAB_STATUS_C in (3,4,5)

          where  ((UPPER(RESULTS_COMP_CMT) like '%GREATER%' and RESULTS_COMP_CMT like '%100,000%') OR RESULTS_COMP_CMT like '%>100,000%')   

      AND UPPER(RESULTS_COMP_CMT) NOT like '%DISREGARD%' AND

          order_results.ORDER_PROC_ID in (31440xxx,3144xxx)

       

       

      order by  order_results.pat_enc_csn_id,specimn_taken_time, order_results.order_proc_id);

      QUIT;

       

       

       

      Thanks

       

      LB