4 Replies Latest reply: Oct 4, 2013 10:29 AM by Frank Kulash RSS

    Extracting data from a string

    Idiot

      Hi,

       

      This is my sample data:

       

      WITH table_data
           AS (SELECT 'ABC12345*Z23*1234*Cheese*24/02/2011' str FROM DUAL
               UNION ALL
               SELECT 'Aasda1ewr3345*A32345*1234*Bread*01/11/2012' str FROM DUAL
               UNION ALL
               SELECT 'dsf31212*TEST*124234*Blue*06/07/2007' str FROM DUAL
               UNION ALL
               SELECT 's342244334*THIS*K31242*White*14/10/2012' str FROM DUAL
               UNION ALL
               SELECT 'DATA43321a*THAT*Y23424*Birmingam*24/02/  2011' str FROM DUAL)
      SELECT str
           , instr(str,'*') v1
           , SUBSTR(str,0,instr(str,'*')-1) v2
        FROM table_data;
      

       

      I can extract the data from before the first *, but I need to also be able to extract the data from between the 1st and 2nd *s, e.g. Z23, A32345, TEST, THIS and THAT from the sample data above.

       

      Any advice would be much appreciated.

       

      Thanks

        • 1. Re: Extracting data from a string
          James in Berkshire

          SELECT str

               , instr(str,'*') v1

               , SUBSTR(str,0,instr(str,'*')-1) v2

               , regexp_substr(str,'\*([^*]*)\*', 1,1,null,1)

            FROM table_data;

          • 2. Re: Extracting data from a string
            Idiot

            Thank you :-)

            • 3. Re: Extracting data from a string
              Greg Spall

              Here's another way to do it, no regular expression needed:

               

              WITH table_data 

                   AS (SELECT 'ABC12345*Z23*1234*Cheese*24/02/2011' str FROM DUAL 

                       UNION ALL 

                       SELECT 'Aasda1ewr3345*A32345*1234*Bread*01/11/2012' str FROM DUAL 

                       UNION ALL 

                       SELECT 'dsf31212*TEST*124234*Blue*06/07/2007' str FROM DUAL 

                       UNION ALL 

                       SELECT 's342244334*THIS*K31242*White*14/10/2012' str FROM DUAL 

                       UNION ALL 

                       SELECT 'DATA43321a*THAT*Y23424*Birmingam*24/02/  2011' str FROM DUAL),

                    w_temp as (

                       SELECT str  ,

                              instr(str,'*') v1,

                              instr(str, '*', 1, 2) v2

                         FROM table_data

                     )

              Select str,

                     substr(str, 1, v1-1)  first_chunk,

                     substr(str, v1+1, v2-v1-1)  second_chunk

                 from w_temp

              /

               

              STR                                           FIRST_CHUNK          SECOND_CHUNK

              --------------------------------------------- -------------------- ------------

              ABC12345*Z23*1234*Cheese*24/02/2011           ABC12345             Z23

              Aasda1ewr3345*A32345*1234*Bread*01/11/2012    Aasda1ewr3345        A32345

              dsf31212*TEST*124234*Blue*06/07/2007          dsf31212             TEST

              s342244334*THIS*K31242*White*14/10/2012       s342244334           THIS

              DATA43321a*THAT*Y23424*Birmingam*24/02/  2011 DATA43321a           THAT

               

               

               

              5 rows selected.

              • 4. Re: Extracting data from a string
                Frank Kulash

                Hi,

                 

                If performance is very important, then don't use regular expressions.  You can get what you need just using SUBSTR and INSTR, like this:

                WITH    got_pos    AS

                (

                    SELECT  str

                    ,       INSTR (str, '*')  AS pos_1

                    ,       INSTR (str, '*', 1, 2) AS pos_2

                    FROM    table_data

                )

                SELECT  str

                ,       SUBSTR ( str

                               , 1

                               , pos_1 - 1

                               ) AS part_1

                ,       SUBSTR ( str

                               , pos_1 + 1

                               , (pos_2 - pos_1) - 1

                               )  AS part_2

                FROM    got_pos

                ;

                In many cases, the differenece in performance won't be noticeable, let alone significant, so you'll propbably want the convenience of regular expressions.