13 Replies Latest reply: Oct 18, 2013 8:03 AM by ranit B RSS

    how to split strings in pl/sql

    dave_414

      PROCEDURE COLUMN_SPLIT (p_def   IN     VARCHAR2,

                                           p_sch          OUT VARCHAR2,

                                           p_table           OUT VARCHAR2,

                                           p_column          OUT VARCHAR2)

         IS

         BEGIN

       

       

            NULL;

         END;

      END;

       

      I want to split p_def by dots, check for 3 elements, and return them in p_sch, p_table and p_column

       

      for example p_sch will be like hello.howare.you.

      I want to split it to
      hello

      howare

      you


      I have very limited knowledge with pl/sql can someone point me in the right direction or maybe show some example.

        • 2. Re: how to split strings in pl/sql
          Karthick_Arp

          Use INSTR and SUBSTR functions.

          • 3. Re: how to split strings in pl/sql
            Ramin Hashimzadeh

            select REGEXP_SUBSTR(s, '[^.]+', 1, 1) a,

                   REGEXP_SUBSTR(s, '[^.]+', 1, 2) b,

                   REGEXP_SUBSTR(s, '[^.]+', 1, 3) c

            from (select 'hello.howare.you' s from dual)

             

            A     B      C

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

            hello howare you

            SQL>

             

            ----

            Ramin Hashimzade

            • 4. Re: how to split strings in pl/sql
              dave_414

              I do because after after i have split whatever is in p_def I will then put it in

              p_sch = hello

              p_table = howare

              p_column = you

              • 5. Re: how to split strings in pl/sql
                Hoek

                I see, I was under the impression you wanted to split into rows (a FAQ on this forum) instead of columns and was too low on coffee/caffeine

                Just follow the pointers from Karthick or Ramin.

                And it never hurts to know you can split into rows as well, ofcourse...

                • 6. Re: how to split strings in pl/sql
                  dave_414

                  thanks for your reply and your example please can you show your example again if you didn't know what was coming into p_def because it will not always be "hello howare you" it can be totally something different and after splitting it how will I get it into

                  p_sch

                  p_table

                  p_column

                   

                  thanks again

                  • 7. Re: how to split strings in pl/sql
                    Ramin Hashimzadeh

                     

                     

                    PROCEDURE COLUMN_SPLIT (p_def   IN     VARCHAR2,

                                                         p_sch          OUT VARCHAR2,

                                                         p_table           OUT VARCHAR2,

                                                         p_column          OUT VARCHAR2)

                       IS

                       BEGIN

                           p_sch    :=  REGEXP_SUBSTR(p_def, '[^.]+', 1, 1);

                           p_table  :=  REGEXP_SUBSTR(p_def, '[^.]+', 1, 2);

                           p_column := REGEXP_SUBSTR(p_def, '[^.]+', 1, 3);

                       END;

                     

                     

                    ----

                    Ramin Hashimzade

                    • 8. Re: how to split strings in pl/sql
                      dave_414

                      Thanks you very much.

                      • 9. Re: how to split strings in pl/sql
                        BrendanP

                        In SQL:

                        WITH data AS (SELECT 'hello.howare.you' str FROM DUAL)
                        SELECT Substr (str, 1, Instr (str, '.', 1) - 1) token_1,
                               Substr (str, Instr (str, '.', 1) + 1, Instr (str, '.', 1, 2) - Instr (str, '.', 1) - 1) token_2,
                               Substr (str, Instr (str, '.', 1, 2) + 1) token_3
                          FROM data
                        

                        In PL/SQL:

                        DECLARE
                        
                          l_str         VARCHAR2(30) := 'hello.howare.you';
                          l_sch         VARCHAR2(30);
                          l_table       VARCHAR2(30);
                          l_column      VARCHAR2(30);
                        
                          PROCEDURE Column_Split (p_def             VARCHAR2,
                                                x_sch           OUT VARCHAR2,
                                                x_table         OUT VARCHAR2,
                                                x_column        OUT VARCHAR2) IS
                            l_dot_1     PLS_INTEGER := Instr (p_def, '.', 1);
                            l_dot_2     PLS_INTEGER := Instr (p_def, '.', 1, 2);
                          BEGIN
                        
                            x_sch         := Substr (p_def, 1, l_dot_1 - 1);
                            x_table       := Substr (p_def, l_dot_1 + 1, l_dot_2 - l_dot_1 - 1);
                            x_column      := Substr (p_def, l_dot_2 + 1);
                        
                          END Column_Split;
                        
                        BEGIN
                        
                          Column_Split (l_str, l_sch, l_table, l_column);
                        
                          DBMS_Output.Put_Line (l_sch);
                          DBMS_Output.Put_Line (l_table);
                          DBMS_Output.Put_Line (l_column);
                        
                        END;
                        
                        • 10. Re: how to split strings in pl/sql
                          dave_414

                          can you explain '[^.]+', 1, 1) to me please especially why the +

                          • 11. Re: how to split strings in pl/sql
                            Ramin Hashimzadeh

                            please read documentation about regular expression.

                             

                            ----

                            Ramin Hashimzade

                            • 12. Re: how to split strings in pl/sql
                              SKP

                              Lets say HELLO.HOW.ARE.YOU

                              [^.]+ ,1,1  --> select all characters till  . character  From 1st position to First occurrence of the .

                              So it will sellect HELLO from the above string .

                              + says match one or more charecter except the . character for this example

                              If you use without +  then  [^.],1,1 then it will match for a single character

                               

                              select regexp_substr('Hello.How .Are.You','[^.]',1,1)  from dual;

                              Output

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

                              H

                              select regexp_substr('Hello.How .Are.You','[^.]+',1,1)  from dual; --Match all characters untill a . charecter occures

                              Output

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

                              Hello

                              • 13. Re: how to split strings in pl/sql
                                ranit B

                                Something like this?

                                 

                                SQL> WITH xx AS
                                  2  (
                                  3    SELECT 'hello.howare.you' txt FROM dual
                                  4  )
                                  5  SELECT
                                  6    SubStr(txt, 1, InStr(txt, '.', 1, 1)-1) p_sch,
                                  7    SubStr(txt, InStr(txt, '.', 1, 1)+1, (InStr(txt, '.', 1, 2)-1) - (InStr(txt, '.', 1, 1))) p_table,
                                  8    SubStr(txt, InStr(txt, '.', 1, 2)+1) p_column
                                  9  FROM xx;

                                SQL> /


                                P_SCH           P_TABLE         P_COLUMN
                                --------------- --------------- ---------------
                                hello           howare          you

                                 

                                But, why are you using a PL/SQL for this?

                                You can use plain SQL and can achieve a huge performance gain. ( "SQL is much faster than PL/SQL" )

                                 

                                Also, please explain the business requirement?

                                Why do you take a text input and split it into different columns? Is it possible to take 3 separate inputs each for the fields?

                                 

                                -- Ranit