3 Replies Latest reply: Sep 4, 2014 11:11 AM by Frank Kulash RSS

    How I can brake the data in this column

    896976

      I have a column that store the data like this

       

      Sarah Kendall Ireland

      First_name,last_name, middle all in one column,column_name

      I can't use

      subtr(column_name,1,5) to extract Sarah

      subtr(column_name,7,7) to extract last_name

      subtr(column_name,15,7) to extract Ireland


      It may work with this case but as we know not all the names are the same length, so I need something to extract from 1 until it hit

      the space (in this case 6) then 7 until it hit the space in this case 14 then 15 to the and of the middle name

      Thank you

        • 1. Re: How I can brake the data in this column
          user12075536123

          set linesize 10000
          col first_name format A10
          col last_name  format A10
          col middle     format A10


          with test as ( select 'Sarah Kendall Ireland' name from dual )
          select
          name,
          substr(name,1                      ,instr(name,' ',1,1) - 1                       ) first_name,
          substr(name,instr(name,' ',1,1) + 1,instr(name,' ',1,2) - instr(name,' ',1,1) - 1 ) last_name,
          substr(name,instr(name,' ',1,2) + 1,length(name)        - instr(name,' ',1,2)     ) middle
          from test;


          NAME                  FIRST_NAME LAST_NAME  MIDDLE
          --------------------- ---------- ---------- ----------
          Sarah Kendall Ireland Sarah      Kendall    Ireland

          • 2. Re: How I can brake the data in this column
            Frank Kulash

            Hi,

             

            Here's another way:

             

            SELECT  name

            ,       REGEXP_SUBSTR (name, '[^ ]+', 1, 1)  AS first_name

            ,       REGEXP_SUBSTR (name, '[^ ]+', 1, 2)  AS last_name

            ,       REGEXP_SUBSTR (name, '[^ ]+', 1, 3)  AS middle

            FROM    test

            ;

            INSTR and SUBSTR will be faster, but maybe not significantly faster.

            • 3. Re: How I can brake the data in this column
              Frank Kulash

              Hi,

               

              If you want to use the INSTR/SUBSTR technique, you can factor out the INSTR computations, like this

               

              WITH    got_pos    AS

              (

                  SELECT  name

                  ,       INSTR (name, ' ', 1, 1)  AS pos_1

                  ,       INSTR (name, ' ', 1, 2)  AS pos_2

                  FROM    test

              )

              SELECT  name

              ,       SUBSTR (name,  1,          pos_1 - 1)            AS first_name

              ,       SUBSTR (name,  pos_1 + 1,  (pos_2 - pos_1) - 1)  AS last_name

              ,       SUBSTR (name,  pos_2 + 1)                        AS middle

              FROM    got_pos

              ;