Results 1 to 9 of 9
  1. #1
    Join Date
    Apr 2012
    Posts
    15

    Unanswered: PL/SQL scripting help !!!

    Hi Guys

    I'm Having trouble competing the following code, I think I've got the logic right but my coding is so bad I need some help. The objective of the code is I have two tables and I have to go through the first table's columns and insert in the correct columns in the second table. Please let me know how this can be fixed.

    Loop through a column called "street1" in the table "test_data" (table and column already created in the database) to check each columns string and place or split the string into a second table called new_data with two column called Newstreetname and Newstreetnumber (table and column already created in the database) .
    If

    "Crn" is found in the String

    Then

    Everything after Crn is added into Newstreetname. Newstreetnumber is null, end.

    Else if

    "PO Box" is found in the street1 string

    Then

    Everything is added into the Newstreetname. Newstreetnumber 1 is null, end

    Else if

    No numbers are found in the street1 string

    Then

    Everything added into Newstreetname. Newstreetnumber is null, end

    Else

    If number is found in street1 string

    Then

    Add street numbers to Newstreetnumber and street name to Newstreetname (I think this part is right.)

    END

    Code

    DECLARE
    cursor c1 is
    SELECT street1
    from helens_data;
    r1 c1%ROWTYPE;
    BEGIN
    FOR r1 IN c1 LOOP

    If

    Instr(r1.street1, ‘Cnr’, 1) >= 1

    Then

    Newstreetname := Substr(r1.street1, Instr(r1.street1, ‘Cnr’, 1)+3);

    Else if

    Instr(r1.street1, ‘PO Box’, 1) >= 1

    Then

    Newstreetname:= Substr(r1.street1, Instr(r1.street1, ‘PO Box’, 1));

    Else if

    REGEXP_ Instr (r1.street1, [\d], 1) = 0

    Then

    Newstreetname:= r1.street1;

    Else

    REGEXP_ Instr (r1.street1, [\d], 1) >= 1

    Then

    Newstreetnumber:= regexp_substr(r1.street1, '\d+(\s|\/)(\d+)?-?(\d+)?(\w {1})?');
    Newstreetname:= regexp_substr(r1.street1, '(\w+\s\w+)$');
    END LOOP;
    END;

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    so many errors!
    ALL variables must be declared before they can be used.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Apr 2012
    Posts
    15
    Hi

    Do I still have to declare them here if they are in a existing table already ? e.g Newstreetname is a varchar (40) in the table new_data.

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >Do I still have to declare them here if they are in a existing table already ? e.g Newstreetname is a varchar (40) in the table new_data.

    multiple problems exist with question above.
    1) where is table NEW_DATE referenced or mentioned in posted code?
    2) even if table was mentioned to which ROW would value be set or changed?
    3) You do not set/change row data by PL/SQL assignment statements.
    4) You change row data via DML (INSERT, UPDATE or DELETE statements).

    I STRONGLY suggest you code your procedures one single PL/SQL statement at a time,
    to ensure correct syntax before proceeding to the next statement.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    In addition to what anacedent has said:

    I also strongly recommend that you read the PL/SQL developer's manual. Your questions show that you have not understood the most basic principles and they are all explained in detail in the manual:

    A quick introduction:
    http://docs.oracle.com/cd/E11882_01/appdev.112/e10766/tdddg_subprograms.htm

    And the full reference:
    http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/toc.htm

  6. #6
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I thought that a Procedure might be unneccesary.

    Try an INSERT statement, like...
    (I don't know each expressions work well or not.)
    Code:
    INSERT INTO new_data
    (Newstreetnumber , Newstreetname)
    SELECT
           CASE
           WHEN Instr(r1.street1, 'Cnr'    , 1) >= 1      THEN
                null
           WHEN Instr(r1.street1, 'PO Box' , 1) >= 1      THEN
                null
           WHEN REGEXP_ Instr (r1.street1, [\d], 1) >= 1  THEN
                regexp_substr(r1.street1, '\d+(\s|\/)(\d+)?-?(\d+)?(\w {1})?')
           ELSE null
           END  /* AS Newstreetnumber */
    
         , CASE
           WHEN Instr(r1.street1, 'Cnr'    , 1) >= 1      THEN
                Substr(r1.street1, Instr(r1.street1, 'Cnr' , 1)+3)
           WHEN Instr(r1.street1, 'PO Box' , 1) >= 1      THEN
                Substr(r1.street1, Instr(r1.street1, 'PO Box' , 1))
           WHEN REGEXP_ Instr (r1.street1, [\d], 1) = 0   THEN
                r1.street1;
           ELSE regexp_substr(r1.street1, '(\w+\s\w+)$')
           END  /* AS Newstreetname */
     FROM  helens_data r1
    ;
    or, by combining WHEN clauses...
    Code:
    INSERT INTO new_data
    (Newstreetnumber , Newstreetname)
    SELECT
           CASE
           WHEN REGEXP_ Instr (r1.street1, [\d], 1) >= 1
            AND Instr(r1.street1, 'Cnr'    , 1)     =  0
            AND Instr(r1.street1, 'PO Box' , 1)     =  0  THEN
                regexp_substr(r1.street1, '\d+(\s|\/)(\d+)?-?(\d+)?(\w {1})?')
           ELSE null
           END  /* AS Newstreetnumber */
    
         , CASE
           WHEN Instr(r1.street1, 'Cnr'    , 1)     >= 1  THEN
                Substr(r1.street1, Instr(r1.street1, ‘Cnr’, 1)+3)
           WHEN Instr(r1.street1, 'PO Box' , 1)     >= 1  THEN
                Substr(r1.street1, Instr(r1.street1, ‘PO Box’, 1))
           WHEN REGEXP_ Instr (r1.street1, [\d], 1) =  0  THEN
                r1.street1
           ELSE regexp_substr(r1.street1, '(\w+\s\w+)$')
           END  /* AS Newstreetname */
     FROM  helens_data r1
    ;
    Last edited by tonkuma; 04-21-12 at 09:08. Reason: Replace last WHEN of Newstreetname by ELSE. Commen out column names of SELECTs. Reverse order of columns of INSERTs.

  7. #7
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I added [CODE] tags to quoted texts.

    If this issue was related with
    Quote Originally Posted by kaidi223 View Post
    Hi Guys

    I wonder if any one can help me, I a bit of a noob. I need to split to a string from one table and split into two columns in another table. There is a few different variety of numbers and rules but no clear delimiter. I was how this can be done. Do I use combo of SUBSTR and INSTR or use if then loops to satisfy all the rules.
    Thanks in advance.

    Table

    Code:
    5 Kent Street 
    3 A lindt Street
    2/15 bold Street 
    9/34-36 carmen Road
    12/5a sandford Street
    Result

    Code:
    Number  |Street
    ----------------
    5          |Kent Street
    3A        |lindt Street
    2/15      |bold Street
    9/34-36 |carmen Road
    12/5a    |sandford Street
    then did you considered the following cases?(I think those rules were not explained.)
    (a) Those two cases contradict "3A |lindt Street".
    3 a lindt street 3 a lindt street
    shop 231 John St shop 231 John St
    Because, a blank was not removed in "3 a" and "shop 231".

    (b) 'Pde' was added.
    368 Cnr Barker & Anzac Barker & Anzac Pde

    in
    Quote Originally Posted by kaidi223 View Post
    Hi

    Thanks for the response, there seems to be more variety where if word in front of number also get split into second column and if Cnr is found then it stop and split the rest into second columns.

    Code:
    COL                            RES1       RES2
    ------------------------------ ---------- ---------------
    5 kent street                 5             kent street
    3 a lindt street               3 a          lindt street
    2/15 bold street             2/15         bold street
    9/34-36 carmen road      9/34-36     carmen road
    12/5a sandford street     12/5a        sandford street
    shop 231 John St           shop 231   John St 
    Cnr 321 Pollock RD                         321 Pollock RD
    368 Cnr Barker & Anzac                   Barker & Anzac Pde

  8. #8
    Join Date
    Apr 2012
    Posts
    15
    Quote Originally Posted by tonkuma View Post
    I added [CODE] tags to quoted texts.

    If this issue was related with

    then did you considered the following cases?(I think those rules were not explained.)
    (a) Those two cases contradict "3A |lindt Street".
    3 a lindt street 3 a lindt street
    shop 231 John St shop 231 John St
    Because, a blank was not removed in "3 a" and "shop 231".

    (b) 'Pde' was added.
    368 Cnr Barker & Anzac Barker & Anzac Pde

    in
    Yeah you are correct on this one again. Result for Shop 231 John St is:
    231 John St, it seems to cut off the first word and just start at the number.

  9. #9
    Join Date
    Apr 2012
    Posts
    15
    Quote Originally Posted by tonkuma View Post
    I added [CODE] tags to quoted texts.

    If this issue was related with

    then did you considered the following cases?(I think those rules were not explained.)
    (a) Those two cases contradict "3A |lindt Street".
    3 a lindt street 3 a lindt street
    shop 231 John St shop 231 John St
    Because, a blank was not removed in "3 a" and "shop 231".

    (b) 'Pde' was added.
    368 Cnr Barker & Anzac Barker & Anzac Pde

    in
    There also needs to be a when statement for shop. If shop is found in the string then found the last number in the string (thats the only case with shop) every thing before shop is inserted to streetnumber and every after inserted to streetname. Any hint on what the regexp for this statement is ?

Tags for this Thread

Posting Permissions

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