3 Replies Latest reply: May 1, 2013 7:32 AM by jamphan RSS

    Extract Field Into 3 Parts

    jamphan
      I have a field that is 3 fields combined in 1 with ## as the seperator. I need to extract the 3 different parts into 3 seperate fields. Below is an example of the field.

      Medical Issue##gets ssi on 3rd of every month##is his only transportation

      What I need to end up with is:
      Field1: Medical Issue
      Field2: gets ssi on 3rd of every month
      Field3: is his only trasportation

      Thanks for any help.

      Edited by: jamphan on Apr 30, 2013 2:19 PM
        • 1. Re: Extract Field Into 3 Parts
          rp0428
          >
          have a field that is 3 fields combined in 1 with ## as the seperator. I need to extract the 3 different parts into 3 seperate fields. Below is an example of the field.

          Medical Issue##gets ssi on 3rd of every month##is his only transportation

          What I need to end up with is:
          Field1: Medical Issue
          Field2: gets ssi on 3rd of every month
          Field3: is his only trasportation
          >
          Here's a hint: use the 'instr' function to locate the delimiters. It returns the position of the delimiter in the string.

          Then use the 'substr' function to extract that part of the string
          with q as (select 'Medical Issue##gets ssi on 3rd of every month##is his only transportation' myColumn from dual)
          select substr(myColumn, 1, instr(myColumn, '##') - 1) field1, myColumn from q
          
          FIELD1     MYCOLUMN
          Medical Issue     Medical Issue##gets ssi on 3rd of every month##is his only transportation
          Just expand that example to extract the 2nd and 3rd strings. The 'occurence' of the delimiter will be 2 and 3 for the 2nd and 3rd strings.

          See the 'INSTR' function in the SQL Language doc
          http://docs.oracle.com/cd/B28359_01/server.111/b28286/functions073.htm
          >
          Purpose

          The INSTR functions search string for substring. The function returns an integer indicating the position of the character in string that is the first character of this occurrence. INSTR calculates strings using characters as defined by the input character set. INSTRB uses bytes instead of characters. INSTRC uses Unicode complete characters. INSTR2 uses UCS2 code points. INSTR4 uses UCS4 code points.

          position is an nonzero integer indicating the character of string where Oracle Database begins the search. If position is negative, then Oracle counts backward from the end of string and then searches backward from the resulting position.

          occurrence is an integer indicating which occurrence of string Oracle should search for. The value of occurrence must be positive. If occurrence is greater than 1, then the database searches for the second occurrence beginning with the second character in the first occurrence of string, and so forth.
          >
          Here is the full extract with the positions broken out separately so you can see what they are doing
          with q as (select 'Medical Issue##gets ssi on 3rd of every month##is his only transportation' myColumn from dual),
             pos1 as (select instr(myColumn, '##') p1 from q),
             pos2 as (select instr(myColumn, '##', p1, 2) p2 from q, pos1)
          select p1, p2, substr(myColumn, 1, p1 - 1) field1,
                 substr(myColumn, p1 + 2, p2 - p1 - 2) field2,
                 substr(myColumn, p2 + 2) field3 from q, pos1, pos2
          
          P1     P2     FIELD1     FIELD2     FIELD3
          14     46     Medical Issue     gets ssi on 3rd of every month     is his only transportation
          You can embed the 'pos1' and pos2' queries as subqueries if you want.
          • 2. Re: Extract Field Into 3 Parts
            chris227
            with data as (
            select
            'Medical Issue##gets ssi on 3rd of every month##is his only transportation' str from dual
            )
            
            select
             regexp_substr(str, '[^#]+', 1, 1) field1
            ,regexp_substr(str, '[^#]+', 1, 2) field1
            ,regexp_substr(str, '[^#]+', 1, 3) field1
            from data
            
            FIELD1     FIELD1     FIELD1
            Medical Issue     gets ssi on 3rd of every month     is his only transportation
            • 3. Re: Extract Field Into 3 Parts
              jamphan
              Thank you Chris227. That worked perfectly!