2 Replies Latest reply on Feb 10, 2019 3:12 PM by mathguy

    Pull out string between two words in string column

    spalato76

      Hi All,

       

      I have a large string column, due to the size of the string I had to set it as a "LONG" datatype.  I'm trying to get a string expression between two words as you'll see in the example below.  I'm trying to get the string expression between the words "IMPRESSIONS:" and "RECOMMENDATIONS:" and once I get the string value between these two words I have to determine if there is any value other than "\.br\" in that string.  There is supposed to be values other than this and if there isn't that means the string is null and there is missing data.  As you'll see there can be multiple occurances of "\.br\", I don't really care about the number of occurances, I just need to know if there is any string other than this otherwise it's considered null and needs to be identified.  The oracle version is 11g and the table has two columns:

       

      column                         datatype

      primary_key                 number

      hl7_message               long

       

      The hl7_message column is the one with the string and here's two examples of the string with info and without:

       

      With info:

       

      \ADVERSE EVENTS: None\.br\\.br\IMPRESSIONS:\.br\1.  Possible residual polyp in ascending colon site (across from ICV)\.br\2.  Transverse colon diminutive polyp\.br\3.  Moderate pan and severe left sided diverticulosis.\.br\\.br\\.br\RECOMMENDATIONS:     A recommendation for ongoing surveillance will be provided\.br\to the referring physician after pathological examination of today's specimens.\.br\The recommendation will be provided within the next 6 - 8 weeks. 

       

      Without info:

       

      \ADVERSE EVENTS: None\.br\\.br\IMPRESSIONS:\.br\\.br\RECOMMENDATIONS:\.br\\.br\_____________________________\.br\

       

      Any help would be appreciated.

       

      Thanks

       

      P.S. the " \.br\"  is just line breaks since the info is being inputed through an online application.

        • 1. Re: Pull out string between two words in string column
          John Thorton

          spalato76 wrote:

           

          Hi All,

           

          I have a large string column, due to the size of the string I had to set it as a "LONG" datatype. I'm trying to get a string expression between two words as you'll see in the example below. I'm trying to get the string expression between the words "IMPRESSIONS:" and "RECOMMENDATIONS:" and once I get the string value between these two words I have to determine if there is any value other than "\.br\" in that string. There is supposed to be values other than this and if there isn't that means the string is null and there is missing data. As you'll see there can be multiple occurances of "\.br\", I don't really care about the number of occurances, I just need to know if there is any string other than this otherwise it's considered null and needs to be identified. The oracle version is 11g and the table has two columns:

           

          column datatype

          primary_key number

          hl7_message long

           

          The hl7_message column is the one with the string and here's two examples of the string with info and without:

           

          With info:

           

          \ADVERSE EVENTS: None\.br\\.br\IMPRESSIONS:\.br\1. Possible residual polyp in ascending colon site (across from ICV)\.br\2. Transverse colon diminutive polyp\.br\3. Moderate pan and severe left sided diverticulosis.\.br\\.br\\.br\RECOMMENDATIONS: A recommendation for ongoing surveillance will be provided\.br\to the referring physician after pathological examination of today's specimens.\.br\The recommendation will be provided within the next 6 - 8 weeks.

           

          Without info:

           

          \ADVERSE EVENTS: None\.br\\.br\IMPRESSIONS:\.br\\.br\RECOMMENDATIONS:\.br\\.br\_____________________________\.br\

           

          Any help would be appreciated.

           

          Thanks

           

          P.S. the " \.br\" is just line breaks since the info is being inputed through an online application.

          LONG  datatype has been deprecated since at least V8 Oracle last Century.

          I won't facilitate Worst Practice coding.

          You should use CLOB datatype instead.

           

          Please click on URL below & provide details as stated in #5 - #9 inclusive

           

          How do I ask a question on the forums?

           

          OraFAQ Forum: SQL & PL/SQL » Pull out string between two words in string column

           

           

          Why so MANY UNANSWERED threads started by you?

           

          spalato76

          • 2. Re: Pull out string between two words in string column
            mathguy

            Don't use the LONG data type.

             

            If you already used the LONG data type, un-use it. If you don't know how to do that, learn (and come back here and ask if you need help).

             

            You should do this regardless of any other question you have, for example the one you asked in the original post in this thread.

             

            *    *    *    *    *    *

             

            Once that's done, here is how you could solve the problem you asked. Assuming the message column is CLOB data type:

             

            with

              prep as (

                select primary_key

                     , hl7_message

                     , instr(hl7_message, 'IMPRESSIONS:') + length('IMPRESSIONS:') as pos1

                     , instr(hl7_message, 'RECOMMENDATIONS:') as pos2

                from   your_table

              )

            select primary_key

            from   prep

            where  dbms_lob.compare(hl7_message, rpad(to_clob('\.br\'), pos2 - pos1, '\.br\'), pos2 - pos1, pos1, 1) = 0

              and  mod(pos2 - pos1, length('\.br\')) = 0

               or  pos2 = pos1

            ;