Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2004
    Posts
    19

    Exclamation Unanswered: PL/SQL help - split an address

    I have a single address column that i want to split.
    For example I have an address with carriage returns like:

    address
    --------------------------------
    Administration Tech Services

    1234 Elm Avenue

    West Building


    I would like a SELECT query to split this address column into 3 like:
    address address2 address3
    ---------- ----------- -----------
    Administration Tech Services 1234 Elm Avenue West Building

  2. #2
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool

    You may need to create a function like this:
    Code:
    Create Or Replace Function Get_Addr
    (P_Line Number, P_Addr Varchar2)
    Return Varchar2 Is
    Type Addr_Typ Is Table Of Varchar2(1000);
    V_Addr Addr_Typ;
    V_Tmp Varchar2(1000);
    I Pls_Integer;
    J Pls_Integer;
    K Pls_Integer;
    L Pls_Integer;
    Begin
      V_Tmp:=Rtrim(P_Addr)||Chr(10);
      For I In 1..P_Line Loop
        V_Addr(I):='';
      End Loop;
      I:=0;
      L:=Length(V_Tmp);
      While (L > 0)
      Loop
        K:=Instr(V_Tmp,Chr(10));
        If K = 0 Then
          Exit;
        End If;
        J:= K-1;
        If J > 0 Then
          I:=I+1;
          V_Addr(I):= Substr(V_Tmp,1,J);
          V_Tmp:=Rtrim(Substr(V_Tmp||' ',K+1));
        End If;
        L:=Length(V_Tmp);
      End Loop;
      Return V_Addr(P_Line);
    End;
    /
    And use it like:
    Code:
    UPDATE MyAddrTab
         SET Address2=Get_Addr(2,Address)
             , Address3=Get_Addr(3,Address);
    COMMIT;
    UPDATE MyAddrTab
         SET Address=Get_Addr(1,Address);
    COMMIT;


    PS: You will need to validate parameters!
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •