This discussion is archived
13 Replies Latest reply: Oct 18, 2013 6:03 AM by ranit B RSS

how to split strings in pl/sql

dave_414 Newbie
Currently Being Moderated

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.

  • 1. Re: how to split strings in pl/sql
    Hoek Guru
    Currently Being Moderated
  • 2. Re: how to split strings in pl/sql
    Karthick_Arp Guru
    Currently Being Moderated

    Use INSTR and SUBSTR functions.

  • 3. Re: how to split strings in pl/sql
    Ramin Hashimzadeh Expert
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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 Guru
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

    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 Expert
    Currently Being Moderated

     

    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 Newbie
    Currently Being Moderated

    Thanks you very much.

  • 9. Re: how to split strings in pl/sql
    BrendanP Journeyer
    Currently Being Moderated

    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 Newbie
    Currently Being Moderated

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

  • 11. Re: how to split strings in pl/sql
    Ramin Hashimzadeh Expert
    Currently Being Moderated

    please read documentation about regular expression.

     

    ----

    Ramin Hashimzade

  • 12. Re: how to split strings in pl/sql
    SKP Journeyer
    Currently Being Moderated

    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 Expert
    Currently Being Moderated

    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points