divide one field into two!

Well, you can try some coding with the substring () function.

The data that you have provided has a lot of problems, which you want to be fixed in the output. So, I dont think, its as simple as just dividing the column into two.

To start with, you can use the following SQL to get the result, which is similar to result that you are looking for. But it is not exactly same. I will need answers to a few more questions to give you a precise answer. :)

Try this ...

SQL> select fld1 from tab_ex;

FLD1
--------------------------------------------------------------------------------
1 Ada Street
1 A Kalinda Street
1/13 Burrawang Street
1/21-23 Chelmsford Road
10/1a Frederick Street
161? Lang Street
368 Cnr Barker & Anzac Pde
5/70-74/ Wilson Street
Shop 2 4-14 Claudia Road
PO Box 558
Lot 4 Mungerie Road
Level 4/65 Albert Avenue
Cnr Warf Rd & Hollywood Rd
Unit 85 51 Little Willandra Rd

14 rows selected.

SQL> select substr(fld1, 1, instr(fld1, ' ')) roadnum, substr(fld1, instr(fld1, ' ')) roadname from tab_ex;

ROADNUM                      ROADNAME
---------------------------  ---------------------------
1                            Ada Street
1                            A Kalinda Street
1/13                         Burrawang Street
1/21-23                      Chelmsford Road
10/1a                        Frederick Street
161?                         Lang Street
368                          Cnr Barker & Anzac Pde
5/70-74/                     Wilson Street
Shop                         2 4-14 Claudia Road
PO                           Box 558
Lot                          4 Mungerie Road
Level                        4/65 Albert Avenue
Cnr                          Warf Rd & Hollywood Rd
Unit                         85 51 Little Willandra Rd

14 rows selected.

Now, note the following :
1. 2nd address, "1 A Kalinda Street" has become "1","A Kalinda Street"
2. 6th address, "161? Lang Street" has become "161?","Lang Street"
3. 7th address, "368 Cnr Barker & Anzac Pde" has become "368","Cnr Barker & Anzac Pde"
4. 8th address, "5/70-74/ Wilson Street" has become "5/70-74/","Wilson Street"
5. 9th address, "Shop 2 4-14 Claudia Road" has become "Shop", "2 4-14 Claudia Road"
6. 10th address, "PO Box 558" has become "PO", "Box 558"
7. 11th address, "Lot 4 Mungerie Road" has become "Lot", "4 Mungerie Road"
8. 12th address, "Level 4/65 Albert Avenue" has become "Level", "4/65 Albert Avenue"
9. 13th address, "Cnr Warf Rd & Hollywood Rd" has become "Cnr", "Warf Rd & Hollywood Rd"
10. 14th address, "Unit 85 51 Little Willandra Rd" has become "Unit", "85 51 Little Willandra Rd"

In all these cases you wanted some different values like :
1. "1A","Kalinda Street" instead of "1","A Kalinda Street"
2. "161","Lang Street" instead of "161?","Lang Street"
3. "","Barker & Anzac Pde" instead of "368","Cnr Barker & Anzac Pde"
4. "5/70-74","Wilson Street" instead of "5/70-74/","Wilson Street"
5. "Shop 2 4-14", "Claudia Road" instead of "Shop", "2 4-14 Claudia Road"
6. "", "PO Box 558" instead of "PO", "Box 558"
7. "Lot 4", "Mungerie Road" instead of "Lot", "4 Mungerie Road"
8. "Level 4/65", "Albert Avenue" instead of "Level", "4/65 Albert Avenue"
9. "", "Warf Rd & Hollywood Rd" instead of "Cnr", "Warf Rd & Hollywood Rd"
10. "House House 85 51", "Little Willandra Rd" instead of "Unit", "85 51 Little Willandra Rd"

Almost all these cases are different, and have to be dealt separately. Do you have a set of rules, by which you can actually divide the road number and road name. You will not be able to have the desired results, unless these rules are clearly stated.

some questions like :
1. When do you concatenate the number and character (string) to formulate the road number.
2. Which characters do you need to strip. (like '/' and '?'), and when and where from do you strip it.
3. What is the rule of Cnr ? If there is a Cnr in the text, do you strip the Cnr and any number before it ?
4. How do you arrive at the shop number ? In case of "Shop 2 4-14 Claudia Road" ? In which cases do you keep the characters before the actual number (intact with the number) to arrive at road number ? for example - Shop/Level etc.
5. Is there a separate rule for Post Boxes (PO Box) ?
6. In which cases do you replace the character strings ? In this case - from Unit to House.

If you answer all these questions, you might find the answer in your questions. :)

Do let me know in case you need any more help on this.

Thanx Mr. Roy

I have clear view regarding my problem on this topic u can check this:
http://www.experts-exchange.com/Databases/Oracle/Q_20935009.html

but still having problem to put datas on 2nd table and exception table, what i am trying to do those unformatted address i want to throw into exception table and amend the it manually after looking at exception table.  Will u please look that link to get more idea
and ur question really helped to think what should i do with this unforammted adress, and now only one option i have is to throw into exception table.
For eg:
i hv employee table
emp_no
emp_adress
emp_tel_no

and table employee_2 table (Data inserted from employee table)
emp_no
emp_road_no
emp_road_name
emp_tel_no

and employee_exception (unformatted data thrown here)
emp_no
emp_adress
emp_tel_no
comments (to know what problem occur while splitting)

Ur question:
some questions like :
1. When do you concatenate the number and character (string) to formulate the road number.
>>number with road number into employee_2 and only with road name or street name into exception with comment exception
2. Which characters do you need to strip. (like '/' and '?'), and when and where from do you strip it.
>>"/" is fine but i am looking to unwanted character like "?" to change with null.
I may use INSTR function can ? INSTR(empaddress,'?') and can i use replace funtion to replcae sucj unwanted character with null;

3. What is the rule of Cnr ? If there is a Cnr in the text, do you strip the Cnr and any number before it ?
Cnr=corner reprents corner so i don;t need Cnr and its number
4. How do you arrive at the shop number ? In case of "Shop 2 4-14 Claudia Road" ? In which cases do you keep the characters before the actual number (intact with the number) to arrive at road number ? for example - Shop/Level etc.
I just like to put such doubtful data into exception table and rest other will do manually
5. Is there a separate rule for Post Boxes (PO Box) ?
>>PO box should be in emp_road_name col or can be put into exception table
6. In which cases do you replace the character strings ? In this case - from Unit to House.
>>unit and house number should be on road_number col and road_name in emp_road_name col

Regards!

Hello Khushi,

I have looked at the link that you have provided. It looks like that you have asked same/similar questions in both :) But you have accepted Raj's answer there, but you still are looking for answers :)

Now in the Q_20935009, everyone involved in the discussion had similar questions, that I had put up. You will have to define the rules clearly for identifying the House/Road No. and House/Road Name.

Since the data is coming from free form Excel, you really would have to find out the most common format of address that is present in the Excel sheet. Define the rule for that common format (Whether space/comma/colon or any other character separated fields, or fixed width fields etc). Any address that does not match the common format, can be thrown into the exception table (as you want). Remember, that you can define more than one rule for this as well. These rules can then be enforced in the procedure that you have written in your other question.

Did you notice the values that have come out from the first SQL that I had provided. Which of them are acceptable to you, and which are'nt. We will try and define some rules below.

My SQL had returned :

1. "1","A Kalinda Street" whereas you wanted "1A","Kalinda Street"
>>On which basis would you know if the Street name is "Kalinda Street", and not "A Kalinda Street". "A Kalinda Street" looks like a very valid name to me. Street name can be more than 2 words, like "Barker & Anzac Pde".

2. "161?","Lang Street" whereas you wanted "161","Lang Street"
>>Getting rid of "?" is not a problem, and can be handled easily. See the usage of REPLACE function that I have described below. But I need to know if I can safely take the first string as the house/road number, and rest of it as the house/road name.

3. "368","Cnr Barker & Anzac Pde" whereas you wanted "","Barker & Anzac Pde"
>>Can we safely say that wherever a string "Cnr" occurs in the address, ignore everything before "Cnr" and put the rest in the house/road name ? I would like to have this as a rule.

4. "5/70-74/","Wilson Street" whereas you wanted "5/70-74","Wilson Street"
>>Same comment as for #2. We can get rid of last character from the road number if it is either '/' or '?'. We can take this as a rule.

5. "Shop 2 4-14", "Claudia Road" instead of "Shop", "2 4-14 Claudia Road"
>>By looking at this any intelligent person can guess that "Shop 2 4-14" is the number, and "Claudia Road" is the street name. Unfortunately computer would not be intelligent enough to make this decision. We will have to tell it how to deal with a string that has "Shop" in it. Just like we did for "Cnr" (corner). A Rule can be, if there is a string "Shop" in it, extract the road number as - 1st character of the string to the character which is the last number (in this case character no. 11, i.e. 4) in the string. So, it will extract "Shop 2 4-14" as road number, and return the rest - "Claudia Road" as the name.

6. "PO", "Box 558" whereas you wanted "", "PO Box 558"
>>Similar to comment for #3. We can have a rule - wherever there is a string "PO Box" in the address, put it all in the road name.
 
7. "Lot", "4 Mungerie Road" whereas you wanted "Lot 4", "Mungerie Road"
>>Similar to comment for #5. We can have a rule - wherever there is a string "Lot" in the address, extract the road number as - 1st character of the string to the character which is the last number (in this case character no. 5, i.e. 4) in the string. So, it will extract "Lot 4" as road number, and return the rest - "Mungerie Road" as the name.

8. "Level", "4/65 Albert Avenue" whereas you wanted "Level 4/65", "Albert Avenue"
>>Similar to comment #5 and #7. Deal with the case where you have "Level" in the address.

9. "", "Warf Rd & Hollywood Rd" instead of "Cnr", "Warf Rd & Hollywood Rd"
>>same as comment #3

10. "House 85 51", "Little Willandra Rd" instead of "Unit", "85 51 Little Willandra Rd"
>>May be you need to things to be done, if you find a string "Unit" in the address. First replace "Unit" with "House". Then extract the House Number and Name using the same method as in #5,#7 and #8

If you find an address which does not belong to any of the above handled cases, send the record into the exception table.

Now you would realise the importance of the 6 questions that I had asked. It was because you will have to take care of 6 different cases (atleast), based on 6 different rules.

Coming on to the question that you asked regarding the INSTR function. "NO" INSTR function will not replace the value as you are expecting. "INSTR" function is used to find out the occurance of a string in another string. It returns the position where it finds the matching string.

The following SQL might give you a better idea of INSTR function, and its usage :
SQL> select instr('PRATIK ROY','A') from dual;

INSTR('PRATIKROY','A')
----------------------
                     3

SQL> select instr('PRATIK ROY','R') from dual;

INSTR('PRATIKROY','R')
----------------------
                     2

SQL> select instr('PRATIK ROY','TIK') from dual;

INSTR('PRATIKROY','TIK')
------------------------
                       4

But you can achieve it using the REPLACE function.

Try this :
SQL> select replace('abcdef','c','x') from dual;

REPLAC
------
abxdef

SQL> select replace ('161?','?','') from dual;

REP
---
161

Does this help now ?

Just for your info, I would be very-very busy with my work for the next 2 days, so would be able to see your answer, and provide further help on this only on Saturday. I Hope it would be OK.

Actually on last question i was waiting for solution but no one come up after that i had to close the question.
So i hv accepted answer and close that question!
Now i hv problem to write procedures atleast i hv something to throw in exception table, still working !
regards

OK. I could take out some time, and have created an anonymous PL/SQL block for you, which wud do exactly what you want.

I had created a table tab_ex with just one field fld1. In your case it is emp_adress of employee table.

I created another table tab_ex1 with two fields ROADNUM and ROADNAME. Just like you have emp_road_no and emp_road_name in employee_2 table. You can now use it as per your convenience. You might want to use the UPPER or LOWER function, so that it would work even if you get "SHOP" instead of "Shop" or "CnR" instead of "Cnr" etc. You will notice that I have hardcoded all the cases that I had previously discussed.

Here is the code ...

declare
    v_roadno   varchar2(50);
    v_roadnm   varchar2(200);
    v_lastnum  varchar(100);
    v_tempnm   varchar2(200);

    cursor c1 is
    select fld1 from tab_ex;

begin
    for c1_rec in c1
    loop
        -- Check the format of the address
        -- Case 1. "Cnr"
        --         ignore everything before "Cnr" and put the rest in the house/road name
        IF INSTR(c1_rec.fld1, 'Cnr') > 0 THEN
            v_roadno := '';
            v_roadnm := substr(c1_rec.fld1, INSTR(c1_rec.fld1, 'Cnr')+ 4);
            goto loop_next;
        END IF;

        -- Case 2. "Shop"
        -- if there is a string "Shop" in it, extract the road number as - 1st character
        -- of the string to the character which is the last number
        IF INSTR(c1_rec.fld1, 'Shop') > 0 THEN
            -- Find the last number in the string
            v_lastnum := substr(trim(translate(upper(c1_rec.fld1),
                         '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ', '0123456789')),-1);
            v_roadno  := substr(c1_rec.fld1,1,INSTR(c1_rec.fld1,v_lastnum,-1));
            v_roadnm := substr(c1_rec.fld1, INSTR(c1_rec.fld1,v_lastnum,-1)+1);
            goto loop_next;
        END IF;

        -- Case 3. "PO Box"
        --         Put everything in road name
        IF INSTR(c1_rec.fld1, 'PO Box') > 0 THEN
            v_roadno := '';
            v_roadnm := c1_rec.fld1;
            goto loop_next;
        END IF;

        -- Case 4. "Lot"
        -- if there is a string "Lot" in it, extract the road number as - 1st character
        -- of the string to the character which is the last number
        IF INSTR(c1_rec.fld1, 'Lot') > 0 THEN
            -- Find the last number in the string
            v_lastnum := substr(trim(translate(upper(c1_rec.fld1),
                         '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ', '0123456789')),-1);

            v_roadno  := substr(c1_rec.fld1,1,INSTR(c1_rec.fld1,v_lastnum,-1));
            v_roadnm := substr(c1_rec.fld1, INSTR(c1_rec.fld1,v_lastnum,-1)+1);
            goto loop_next;
        END IF;

        -- Case 5. "Level"
        -- if there is a string "Level" in it, extract the road number as - 1st character
        -- of the string to the character which is the last number
        IF INSTR(c1_rec.fld1, 'Level') > 0 THEN
            -- Find the last number in the string
            v_lastnum := substr(trim(translate(upper(c1_rec.fld1),
                         '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ', '0123456789')),-1);

            v_roadno  := substr(c1_rec.fld1,1,INSTR(c1_rec.fld1,v_lastnum,-1));
            v_roadnm := substr(c1_rec.fld1, INSTR(c1_rec.fld1,v_lastnum,-1)+1);
            goto loop_next;
        END IF;

        -- Case 6. "Unit"
        -- First replace "Unit" with "House". Then extract the House Number using the same method as above
        IF INSTR(c1_rec.fld1, 'Unit') > 0 THEN
            -- Find the last number in the string
            v_tempnm := REPLACE(c1_rec.fld1,'Unit','House');
            v_lastnum := substr(trim(translate(upper(v_tempnm),
                         '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ', '0123456789')),-1);

            v_roadno  := substr(v_tempnm,1,INSTR(v_tempnm,v_lastnum,-1));
            v_roadnm := substr(v_tempnm, INSTR(v_tempnm,v_lastnum,-1)+1);
            goto loop_next;
        END IF;

        -- If none of the above cases, then its the normal address.
        v_roadno := substr(c1_rec.fld1, 1, instr(c1_rec.fld1, ' '));
        v_roadnm := substr(c1_rec.fld1, instr(c1_rec.fld1, ' '));

        <<loop_next>>
        v_roadno := trim(v_roadno);
        v_roadnm := trim(v_roadnm);
        -- last thing, check if the last character of road number is '/' or '?'. Strip if this is the case
        IF substr(v_roadno,-1) = '/' OR substr(v_roadno,-1) = '?' THEN
            v_roadno := substr(v_roadno,1,length(v_roadno)-1);
        END IF;
        dbms_output.put_line(v_roadno || chr(9) || chr(9) || '|' || chr(9) || v_roadnm);
        INSERT INTO tab_ex1 VALUES(v_roadno, v_roadnm);
    end loop;
end;
/

This should help.

The only problem would be for address like "1 A Kalinda Street" where you will get "1","A Kalinda Street" whereas you wanted "1A","Kalinda Street".
I Repeat myself ...
>>On which basis would you know if the Street name is "Kalinda Street", and not "A Kalinda Street". "A Kalinda Street" looks like a very valid name to me. Street name can be more than 2 words, like "Barker & Anzac Pde".

All other cases have been taken care of. This is the output from tab_ex1 table after executing the above code ...

SQL> Select roadnum, roadname from tab_ex1;

ROADNUM              ROADNAME
-------------------- --------------------------------------------------
1                    Ada Street
1                    A Kalinda Street
1/13                 Burrawang Street
1/21-23              Chelmsford Road
10/1a                Frederick Street
161                  Lang Street
                     Barker & Anzac Pde
5/70-74              Wilson Street
Shop 2 4-14          Claudia Road
                     PO Box 558
Lot 4                Mungerie Road
Level 4/65           Albert Avenue
                     Warf Rd & Hollywood Rd
House 85 51          Little Willandra Rd

14 rows selected.

Give me higher points if possible ... ;) just kidding ...

Thanx for the answer...but still i hvn;t run ur code.
Does this code really handles if i hv huge data records.

Original Data                                                 Conversion
FULLADDRESS                                       ROADNUMBER      ROADNAME
1 Ada Street                                          1                      Ada Street
1 A Kalinda Street                                  1A                     Kalinda Street
1/13 Burrawang Street                           1/13                  Burrawang Street
1/21-23 Chelmsford Road                        1/21-23            Chelmsford Road
10/1a Frederick Street                           10/1a                 Frederick Street
161? Lang Street                                    161                   Lang Street
368 Cnr Barker & Anzac Pde                                            Barker & Anzac Pde
5/70-74/ Wilson Street                           5/70-74              Wilson Street
Shop 2 4-14 Claudia Road                      Shop 2 4-14        Claudia Road
PO Box 558                                                                     PO Box 558
Lot 4 Mungerie Road                               Lot 4                  Mungerie Road
Level 4/65 Albert Avenue                         Level 4/65          Albert Avenue
Cnr Warf Rd & Hollywood Rd                                            Warf Rd & Hollywood Rd
Unit 85 51 Little Willandra Rd                 House 85 51         Little Willandra Rd
Suite 2 Ground Floor                                                        EXCEPTION
Street                                                                             EXCEPTION

i hvn;t yet run ur code, but how to deal with exception like address with only "street", suite 2 ground floor, i want to throw it into exception table and all data same as original table with comments on comment row.

tbl sample

customer

custnum
custaddress
phone


customer1

custnum
cust_road_num
cust_road_name
phone

customer_exception
custnum
custaddress
phone
comment

sorry its my mistake house and unit is same so i don;t need replace function to replce unit by house.
can i insert by doing this into exception table just example: (field name may not be same just concept please look at it)

procedure test123    as  
  begin  
  for x in (select * from customer)  
 loop  
  insert into customer1(id,name,street_no,street_name,phone)  
 values(x.id,x.name,substr(x.address,1,instr(x.address,' ',1)-1),substr(x.address,instr(x.address,' ',1)+1),x.phone);  
  end loop;  
 commit;  

...can i add exception or else statement to throw data into exception table

coz i like to do by using procedure or function...

and sure i will raise the points coz it getting huge and huge!
50 to 110 heheh

What is your definition of huge data ? How many rows are you expecting ? I dont think this procedure will have problem.

As for the exception handling,
1. How can I stop "Ground Floor" from being recognised as a street name ? Can you define a rule saying that the road name can be treated as valid, only if it has one of the following :
Rd, Road, Avenue, Street, Pde or PO BOX
2. As for the "street", you might want to add a rule saying that the road name should contain 2 or more words, like 'abc street' or 'xyz & abc Pde' etc.

You can add these two checks at the end, just before inserting the record in the main table. In case the above two checks fail, then insert the record in the exception table, else insert into the main table.

Hope this helps.

You should execute my script, to see how it is working. Things might become more clearer, and you may be able to solve the new issues yourself. The anonymous PL/SQL can be coverted into a procedure (as you like creating procedures :)). Let me know if you need help in doing that.

In the meanwhile I have done the exception handling in my code as well. To see my codes working, I believe you should create the tables, as I have created.
TAB_EX : fld1 varchar2(250)
TAB_EX1 : roadnum varchar2(50), roadname varchar2(200)
TAB_EX_ERR : fld1 varchar2(250), errmsg varchar2(200)

Insert the addresses in TAB_EX table, and then run the following code (Note the changes after <<loop_next>>:

declare
    v_roadno   varchar2(50);
    v_roadnm   varchar2(200);
    v_lastnum  varchar(100);
    v_tempnm   varchar2(200);

    cursor c1 is
    select fld1 from tab_ex;

--10* create table tab_ex1 (roadnum varchar2(50), roadname varchar2(200))
--SQL> create table tab_ex_err (fld1 varchar2(1000), errmsg varchar2(200));
begin
    for c1_rec in c1
    loop
        -- Check the format of the address
        -- Case 1. "Cnr"
        --         ignore everything before "Cnr" and put the rest in the house/road name
        IF INSTR(c1_rec.fld1, 'Cnr') > 0 THEN
            v_roadno := '';
            v_roadnm := substr(c1_rec.fld1, INSTR(c1_rec.fld1, 'Cnr')+ 4);
            goto loop_next;
        END IF;

        -- Case 2. "Shop"
        -- if there is a string "Shop" in it, extract the road number as - 1st character
        -- of the string to the character which is the last number
        IF INSTR(c1_rec.fld1, 'Shop') > 0 THEN
            -- Find the last number in the string
            v_lastnum := substr(trim(translate(upper(c1_rec.fld1),
                         '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ', '0123456789')),-1);
            v_roadno  := substr(c1_rec.fld1,1,INSTR(c1_rec.fld1,v_lastnum,-1));
            v_roadnm := substr(c1_rec.fld1, INSTR(c1_rec.fld1,v_lastnum,-1)+1);
            goto loop_next;
        END IF;

        -- Case 3. "PO Box"
        --         Put everything in road name
        IF INSTR(c1_rec.fld1, 'PO Box') > 0 THEN
            v_roadno := '';
            v_roadnm := c1_rec.fld1;
            goto loop_next;
        END IF;

        -- Case 4. "Lot"
        -- if there is a string "Lot" in it, extract the road number as - 1st character
        -- of the string to the character which is the last number
        IF INSTR(c1_rec.fld1, 'Lot') > 0 THEN
            -- Find the last number in the string
            v_lastnum := substr(trim(translate(upper(c1_rec.fld1),
                         '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ', '0123456789')),-1);

            v_roadno  := substr(c1_rec.fld1,1,INSTR(c1_rec.fld1,v_lastnum,-1));
            v_roadnm := substr(c1_rec.fld1, INSTR(c1_rec.fld1,v_lastnum,-1)+1);
            goto loop_next;
        END IF;

        -- Case 5. "Level"
        -- if there is a string "Level" in it, extract the road number as - 1st character
        -- of the string to the character which is the last number
        IF INSTR(c1_rec.fld1, 'Level') > 0 THEN
            -- Find the last number in the string
            v_lastnum := substr(trim(translate(upper(c1_rec.fld1),
                         '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ', '0123456789')),-1);

            v_roadno  := substr(c1_rec.fld1,1,INSTR(c1_rec.fld1,v_lastnum,-1));
            v_roadnm := substr(c1_rec.fld1, INSTR(c1_rec.fld1,v_lastnum,-1)+1);
            goto loop_next;
        END IF;

        -- Case 6. "Unit"
        -- First replace "Unit" with "House". Then extract the House Number using the same method as above
        IF INSTR(c1_rec.fld1, 'Unit') > 0 THEN
            -- Find the last number in the string
            v_tempnm := REPLACE(c1_rec.fld1,'Unit','House');
            v_lastnum := substr(trim(translate(upper(v_tempnm),
                         '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ', '0123456789')),-1);

            v_roadno  := substr(v_tempnm,1,INSTR(v_tempnm,v_lastnum,-1));
            v_roadnm := substr(v_tempnm, INSTR(v_tempnm,v_lastnum,-1)+1);
            goto loop_next;
        END IF;

        -- If none of the above cases, then its the normal address.
        v_roadno := substr(c1_rec.fld1, 1, instr(c1_rec.fld1, ' '));
        v_roadnm := substr(c1_rec.fld1, instr(c1_rec.fld1, ' '));

        <<loop_next>>
        v_roadno := trim(v_roadno);
        v_roadnm := trim(v_roadnm);
        -- last thing, check if the last character of road number is '/' or '?'. Strip if this is the case
        IF substr(v_roadno,-1) = '/' OR substr(v_roadno,-1) = '?' THEN
            v_roadno := substr(v_roadno,1,length(v_roadno)-1);
        END IF;
        IF instr(v_roadnm,' ') > 0 THEN
            -- If road name has more than one word then check if it has
            -- one of the valid values - Rd, Road, Avenue, Street, Pde or PO BOX
            IF instr(v_roadnm, 'Rd') > 0
            OR instr(v_roadnm, 'Road') > 0
            OR instr(v_roadnm, 'Avenue') > 0
            OR instr(v_roadnm, 'Ave') > 0
            OR instr(v_roadnm, 'Street') > 0
            OR instr(v_roadnm, 'Pde') > 0
            OR instr(v_roadnm, 'PO Box') > 0
            THEN
                dbms_output.put_line(v_roadno || chr(9) || chr(9) || '|' || chr(9) || v_roadnm);
                INSERT INTO tab_ex1 VALUES(v_roadno, v_roadnm);
            ELSE
                dbms_output.put_line(v_roadno || chr(9) || chr(9) || '|' || chr(9) || v_roadnm || '****Problem');
                INSERT INTO tab_ex_err VALUES(c1_rec.fld1,'Invalid Road Name');
            END IF;
        ELSE
            dbms_output.put_line(v_roadno || chr(9) || chr(9) || '|' || chr(9) || v_roadnm || '****Problem');
            INSERT INTO tab_ex_err VALUES(c1_rec.fld1,'Invalid address format');
        END IF;

    end loop;
end;
/

I will try pratik, first i hv to familiar with ur code and if there is any problem i will let u know. Thanx pratik

Have u tested ur code?

isn't it better to modularise the code and call from one or two procedure? please advice me

1. "1","A Kalinda Street" whereas you wanted "1A","Kalinda Street"
>>On which basis would you know if the Street name is "Kalinda Street", and not "A Kalinda Street". "A Kalinda Street" looks like a very valid name to me. Street name can be more than 2 words, like "Barker & Anzac Pde".

2. "161?","Lang Street" whereas you wanted "161","Lang Street"
>>Getting rid of "?" is not a problem, and can be handled easily. See the usage of REPLACE function that I have described below. But I need to know if I can safely take the first string as the house/road number, and rest of it as the house/road name.

3. "368","Cnr Barker & Anzac Pde" whereas you wanted "","Barker & Anzac Pde"
>>Can we safely say that wherever a string "Cnr" occurs in the address, ignore everything before "Cnr" and put the rest in the house/road name ? I would like to have this as a rule.

4. "5/70-74/","Wilson Street" whereas you wanted "5/70-74","Wilson Street"
>>Same comment as for #2. We can get rid of last character from the road number if it is either '/' or '?'. We can take this as a rule.

5. "Shop 2 4-14", "Claudia Road" instead of "Shop", "2 4-14 Claudia Road"
>>By looking at this any intelligent person can guess that "Shop 2 4-14" is the number, and "Claudia Road" is the street name. Unfortunately computer would not be intelligent enough to make this decision. We will have to tell it how to deal with a string that has "Shop" in it. Just like we did for "Cnr" (corner). A Rule can be, if there is a string "Shop" in it, extract the road number as - 1st character of the string to the character which is the last number (in this case character no. 11, i.e. 4) in the string. So, it will extract "Shop 2 4-14" as road number, and return the rest - "Claudia Road" as the name.

6. "PO", "Box 558" whereas you wanted "", "PO Box 558"
>>Similar to comment for #3. We can have a rule - wherever there is a string "PO Box" in the address, put it all in the road name.
 
7. "Lot", "4 Mungerie Road" whereas you wanted "Lot 4", "Mungerie Road"
>>Similar to comment for #5. We can have a rule - wherever there is a string "Lot" in the address, extract the road number as - 1st character of the string to the character which is the last number (in this case character no. 5, i.e. 4) in the string. So, it will extract "Lot 4" as road number, and return the rest - "Mungerie Road" as the name.

8. "Level", "4/65 Albert Avenue" whereas you wanted "Level 4/65", "Albert Avenue"
>>Similar to comment #5 and #7. Deal with the case where you have "Level" in the address.

9. "", "Warf Rd & Hollywood Rd" instead of "Cnr", "Warf Rd & Hollywood Rd"
>>same as comment #3

10. "House 85 51", "Little Willandra Rd" instead of "Unit", "85 51 Little Willandra Rd"
>>May be you need to things to be done, if you find a string "Unit" in the address. First replace "Unit" with "House". Then extract the House Number and Name using the same method as in #5,#7 and #8

Yha all the rules specified are correct, only i hv made mistake is #10, correct data is:
Unit 85 51 Little willandra rd, conversion should be like "Unit 85 51", "Little Willandra rd".

I am trying ur code but geting confuse with whole code, so trying to break it and call by procedure can i do like that?

I understand pratik you are busy, take ur time, have fun!

Khushi, I have tested my code, and its working as per your expectations. What is the confusion in my code ? I had put comments at appropriate places to make the code more understandable.

I had put the solution for point number 10 a well, where I am replace Unit by House. You can remove that replace if you dont want it now.

You can definitely break this code in smaller functions/procedures if you are comfortable with that approach. I felt that since we are just doing one activity (i.e., breaking addresses into road name/road number), and handling various cases, we should do it in one procedure(PL/SQL Block) only.

Have you created the tables that I had asked you to ? Once the tables are created, you can insert the test addresses in the tab_ex, and just run the script that I have given. You can test it to your satisfaction.

Take your time and understand the code. I dont think you can categorise this code as a complicated one. Feel free to ask any more questions.

Thanx Pratik for ur support through out my task! muchly appreiciated

I am now creating all the tables u hv told me to, and running codes, i will let u know how i will go!

Yha i hv created tables and tested it works perfectly fine with out error. WOW!
But this whole code is too big actually i want to modularise it (into small block and call from one main procedure)
I may need to ask more question so i am raising points, coz i dont; want to start another question i would like to continue here!
In this case if i hv something to match from another table can i do on this block!

like if i add one filed in tab_ex table which is name of place (district), that identifies where that person pin code belongs. like i hv pin code table : for eg:
pin_code:
pincode_no number,
district
state

now my tab_ex table looks like:
fld1 varchar2(50),
district varchar2(250),
..........................no pincode field in this table
......

and
tab_ex1
roadnum varchar2(50),
roadname varchar2(200),
district
pincode_no...............................pincode field on this table (those rows that can be successfully return on this table shoudl have pincode value, retrieve from pincode table after matching with district name )

i know how SQL works on this case:
for eg:
select roadnum,roadname,tab_ex.district,pincode.pincode_no
from tab_ex,pincode
where tab_ex.district=pincode.district

so this will match the district on each other table is that rite i am reckoning.

So i should be able to insert into tab_ex1 with pincode value of respective district  from tab_ex. but tab_ex_err remains same as tab_ex don;t contain pincode field only error messgae field that u hv already. coz when we can;t split the address i think its not worth to get pin. right?

Please advice regarding this problem!
Thanx.

with that 14 data it works perfectly alrite. but i hv some more data that needs to be analyse like u did before, like u hv set rules for such datas.

one more rule what i hv found is, differnce between personal address and company address,
I am putting how my original table looks like:
for eg:
TAB_EX
id,
name
company_name
fld1 (fulladdress but as u hv called it fld1 so it will be easier to know u)
district

Like in my some rows name field's value is null, and only company name is there, on that case company address can only contain roadname.
road address does not have to have street number if it is company address Only privatre address needs its street number and name to be accepted?
This case applies when there is only name of street not number, ur code exaclty does what i need like throws data into exception table those only have street name, but when it is not personal address (its company address) then its valid should be in tab_ex1 in roadname col.
We know this if name field is null and name of company in company field is available!

and this part of ur code

IF instr(v_roadnm, 'Rd') > 0
            OR instr(v_roadnm, 'Road') > 0
            OR instr(v_roadnm, 'Avenue') > 0
            OR instr(v_roadnm, 'Ave') > 0
            OR instr(v_roadnm, 'Street') > 0
            OR instr(v_roadnm, 'Pde') > 0
            OR instr(v_roadnm, 'PO Box') > 0

i hv added few more words to check like Hwy, Drive, is that fine? does it checks similarly as it does with Road, Avenue etc.

and...
in this part of code
 THEN
                dbms_output.put_line(v_roadno || chr(9) || chr(9) || '|' || chr(9) || v_roadnm);
                INSERT INTO tab_ex1 VALUES(v_roadno, v_roadnm);
            ELSE
                dbms_output.put_line(v_roadno || chr(9) || chr(9) || '|' || chr(9) || v_roadnm || '****Problem');
                INSERT INTO tab_ex_err VALUES(c1_rec.fld1,'Invalid Road Name');
            END IF;
        ELSE
            dbms_output.put_line(v_roadno || chr(9) || chr(9) || '|' || chr(9) || v_roadnm || '****Problem');
            INSERT INTO tab_ex_err VALUES(c1_rec.fld1,'Invalid address format');
        END IF;

how can i insert whole data beside two splitted data, like
insert into tab_ex1(id,name,company,roadnum(v_roadno),roadname(v_roadnm),postcode) select id,name,..................from tab_ex but i am confused how should i do ?

Let me tell you, this code is not so big, as you think it is. There are a lot of comments in it, which makes it look like a big code. The comments were added for your benefit, and you can get rid of them if you want your code to look shorter. Apart from that, I have indented the code, and have spaces in between logically different cases, to make it more readable.

You have added a lot more stuff to your question, and I am thoroughly confused :) To avoid confusions, let me restate the additional stuff that you want. Correct me if I am wrong, or let me know if this is what you want :
1. Additional Column District in Tab_Ex : This District would be existing in PINCODES table.
2. Additional Column Company Name in Tab_Ex : Rule->If the company name exists, then allow roadname (ignore the check that throws it in exception table if only roadname is provided).
3. A table PINCODES, which will have district, Pin code, and state name. : Master table, which would be referred to fetch the value of PINCODE based on District.
4. Additional Columns Distict and Pin Code in Tab_Ex1. District would come from Tab_Ex1, whereas Pin Code would come from PINCODES table, based on District.
5. Few additional valid Street Names like Drive, Hwy etc. : Yes you can add them to that check, and it would work fine.

This is how the final structures of the three tables would look like :
SQL> desc tab_ex
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                                 NUMBER
 NAME                                               VARCHAR2(50)
 COMP_NAME                                          VARCHAR2(50)
 FLD1                                               VARCHAR2(250)
 DISTRICT                                           VARCHAR2(50)

SQL> desc tab_ex1
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                                 NUMBER
 NAME                                               VARCHAR2(50)
 COMP_NAME                                          VARCHAR2(50)
 ROADNUM                                            VARCHAR2(50)
 ROADNAME                                           VARCHAR2(200)
 POSTCODE                                           VARCHAR2(30)

SQL> desc tab_ex_err
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                                 NUMBER
 NAME                                               VARCHAR2(50)
 COMP_NAME                                          VARCHAR2(50)
 FLD1                                               VARCHAR2(250)
 DISTRICT                                           VARCHAR2(50)
 ERRMSG                                             VARCHAR2(200)

SQL>

Let me know if I am right in understanding your additional questions, so that I could provide the answers.

Also the PINCODEs table :
SQL> desc tab_pins
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 POSTCODE                                           VARCHAR2(30)
 DISTRICT                                           VARCHAR2(50)
 STATE                                              VARCHAR2(30)

A few more questions :
1. What happens in case the name and comp_name both are populated ?
2. Address like "Barker & Anzac Pde" and "Warf Rd & Hollywood Rd" would be valid in case a company name exists, else they would be treated as invalid ?
3. Address like "Street" would be treated as invalid even if there is a company name attached to the address, as "Street" is not a valid street name.
4. Addresses that have "PO Box" would be valid for personal addresses as well ?

some more questions :)
1. What if there is a road/street number entered for the company address, like "2 greenwood street" I think it would be valid.
2. What if the person's name as well as company's name is missing from the address. I think you might want to ignore such records.
3. What if the district is missing from the record ? No PIN would be allocated to the address.
4. What if the district has two reords in the PINCODES table ? I think it would be enforced in the table, and case like this should never occur.
5. What if the District's record is not found in the PINCODES table ?

Yha..happy to see ur posting
Yha ur code is very well formatted and with comment and indented, what i am looking for. But i am talking about can i break this code into smaller procedure and call from main procedure or function, so that i fulfill the definition of reusable, modularise code.

Yha the table created is exactly what i am looking but problem is that my pincodes table data is too huge so i am dealing with specific data sorry i hv missed one field in PINCODES table that is category
now it looks like:
SQL> desc tab_pins
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 POSTCODE                                           VARCHAR2(30)
 DISTRICT                                           VARCHAR2(50)
 STATE                                              VARCHAR2(30)
CATEGORY                                        VARCHAR2(40)

Coz i can't match tab_ex and pincode table becoz of large in size so most of the customer they are in tab_ex are for suppose "West Bengal" state and their category is "Courier service". did u check my above SQL statement?
i think i need to add thier:
state='West Bengal'
and category like '%Courier service%'
right?

Now the question u hv put upon me-
1. What happens in case the name and comp_name both are populated ?
>>Its fine exactly what u hv did before if its incase of both., only for company, nto for private address (only private address needs road number)
2. Address like "Barker & Anzac Pde" and "Warf Rd & Hollywood Rd" would be valid in case a company name exists, else they would be treated as invalid ?
>>Yes!
3. Address like "Street" would be treated as invalid even if there is a company name attached to the address, as "Street" is not a valid street name.
>>yah only street is not valid name, even if it is company coz street doesn't make sense.
4. Addresses that have "PO Box" would be valid for personal addresses as well ?
Yha, just go like null and POBOX for roadnum and roadname

Thanx for taking ur time to read my postings!
Regards

some more questions :)
1. What if there is a road/street number entered for the company address, like "2 greenwood street" I think it would be valid.
>>yha! i think so for its valid
2. What if the person's name as well as company's name is missing from the address. I think you might want to ignore such records.
>>Yha throw into the tab_ex_err with some missing errmsg
3. What if the district is missing from the record ? No PIN would be allocated to the address.
>>yha thanx for finding out that problem i just faced that problem, if that is the case then simply i wanna throw it into tab_ex_err table and if there are 2 PIN for same district that will also goes to tab_ex_err table.
4. What if the district has two reords in the PINCODES table ? I think it would be enforced in the table, and case like this should never occur.
>>Wow, u have found every problem, sorry i didn't read whole question at a time so this answer is included in Q3 hehe.
5. What if the District's record is not found in the PINCODES table ?
>>Throw it into tab_ex_err table.

Regards!

What is the significance of Category ? Will the PIN code be dependent on category ? Is'nt District and Pin Code having 1-to-1 relation ?
Can you give a sample data for Pin_codes table ?
Will it be ....

POSTCODE   DISTRICT      STATE              CATEGORY
=======   ========   ======          =====
1234455       Wellington     W.Bengal          Courier Service
1234456       Wellington     W.Bengal          IPS

Will there be separate post code for Courier Service and Indian Postal Service (IPS) ??

I thought it might be :
POSTCODE   DISTRICT      STATE              CATEGORY
=======   ========   ======          =====
1234455       Wellington     W.Bengal          Courier Service
1234455       Wellington     W.Bengal          IPS

same for the two categories, as they belong to the same District.

Please Clarify.

Yha u r right, coz my tab_ex table (actually it has more than thousands rows) is only dealing with 'W.bengal' state and Courier Service category, so when ever you will match to get post code from PINCODE table u have to care only that state and category, so it will check few rows from thousands and thousands of POSTCODES.
yha they should have 1-1 relation but one district can hv more than 1 PINCODE that is case, but its fine if one districts retrieves more than 1 PIN then should be in tab_ex_err table.

from ur example postcode 1234455 can have more than 1 district. can various category so we hv to match with that category and state so most of the tab_ex table falls under that category.

is it good idea to use min(district) or where rownum < 2

What if the address has any other state apart from West Bengal ? Do you want me to put it in Exceptions table ?

In the PINCODES table...For State = 'W.Bengal' (suppose) and Category = 'Courier Service', 2-3 postcodes are repeated for different districts

even after filtering, i think we should better throw it into tab_ex_err table.
Regards

What if the address has any other state apart from West Bengal ? Do you want me to put it in Exceptions table ?
>>as we are only dealing with w.bengal but if there is by chance then should be throw into exception table.

Regards

I did'nt get your last point. Please re-phrase.

As we are only dealing with "W.bengal" state, so i think when we filter using state and category tab_ex's address falls under that w.bengal state. Yes as u hv mentioned it goes to exception table coz we can;t have other state name only w.bengal.

Is that clear if not please ask me!

I was talking about your comment "In the PINCODES table...For State = 'W.Bengal' (suppose) and Category = 'Courier Service', 2-3 postcodes are repeated for different districts
even after filtering, i think we should better throw it into tab_ex_err table."
Please re-phrase this one for me.

oh! sorry

I mean to say that i hv matched the suburb from tab_ex and PINCODES table (using state and category as my filter) there are only 2 or 3 repeated postcodes for same district.
So if that is the case i would like to throw that into tab_ex_err table
Regards

Am i disturbing ur cricket? heheh..........

Yes, cricket is going on superb :) I am just watching the scores on the cricinfo.com :)

As for the comments, I think what you mean is :
1. The district in address will definitely be of West Bengal and of "Courier Service". If its anything else, it should go to Exception table.
2. In case there are more than one records found, after filtering for West Bengal and Courier Service, I still have to throw that record in the Exception table.

I am re-writing the code, with some functions procedures as you wanted :) Would present the code after I am through with the coding and testing :)

As for the comments, I think what you mean is :
1. The district in address will definitely be of West Bengal and of "Courier Service". If its anything else, it should go to Exception table.
>>Yes, with comment like "postcode problem" somethinbg like that!
2. In case there are more than one records found, after filtering for West Bengal and Courier Service, I still have to throw that record in the Exception table.
>>Yes, absolutely
regards!

Pratik,
Can you break that long code into few small procedures or function and call and do my work from one main procedure/function.
Cause my requirement is modular code, as much as possible!
Regards!

After you run the code, you can expect the following in TAB_EX1 :

        ID NAME                 COMP_NAME                 ROADNUM    ROADNAME                       POSTCODE
---------- -------------------- ------------------------- ---------- ------------------------------ ----------
         1 Alan Border                                    1          Ada Street                     123345
         2 Steve Bucknor                                  1          A Kalinda Street               123345
         3 Tony Day                                       1/13       Burrawang Street               123345
         4 Phil Hampson                                   1/21-23    Chelmsford Road                123345
         5 John Reid                                      10/1       a Frederick Street             123345
         6 Steve Wright                                   161        Lang Street                    123345
         8 Greg Chapman                                   5/70-74    Wilson Street                  123345
         9 Paul Radcliffe                                 Shop 2 4-1 Claudia Road                   123345
        10 George Hemmingway                                         PO Box 558                     123345
        11 Dave Mathews                                   Lot 4      Mungerie Road                  123345
        12 Jonty Rhodes                                   Level 4/65 Albert Avenue                  123345
        14 John Davies                                    Unit 85 51 Little Willandra Rd            123345
        17                      Anzac Solicitors                     Burwood Road                   123345
        18                      Joe & Joyce Travels                  Paramatta Street               123345
        20                      Greenwood Technologies               Cranston Drive                 123345
        21                      Haywire News Ltd                     Martindale Road                123345
        24 Michael Chang                                  15         Ada Street                     123348
        25 Chris Gayle                                    18         Ada Street                     123350

18 rows selected.

And following records would be present in TAB_EX_ERR :

        ID NAME             COMP_NAME  FLD1                           DISTRICT     ERRMSG
---------- ---------------- ---------- ------------------------------ ------------ ----------------------------
         7 Jason Gillespie             368 Cnr Barker & Anzac Pde     Salt Lake    Road Number not provided for personal address
        13 Clive Llyode                Cnr Warf Rd & Hollywood Rd     Salt Lake    Road Number not provided for personal address
        15 Stephen Matthews            Suite 2 Ground Floor           Salt Lake    Invalid Road Name
        16 Chris Harris                Street                         Salt Lake    Invalid address format
        19 Ruth Hughes                 Harmondsworth Street           Salt Lake   Road Number not provided for personal address
        22 Mark Jones                  12 Ada Street                  Wellington   No Data Found for District in PINCODES table
        23 Peter Radcliffe             14 Ada Street                  Bhubneshwar  No Data Found for District in PINCODES table

7 rows selected.

Hope this is what you wanted. I suggest that you test this with your set of data also, after  you have tested it with the data that I have suggested.

Let me know how you feel about it.

All the best !!

Thanx Pratik i will test it! and let u know the feedback once again thanx a lot!

You are welcome :)

I am bit confused with ur code coz it starts with .......

anonymous block....

and then i found procedure followed by cursor
can u explain me what's going there in the code please

Can u specify me which functin/procedure does what stuff?

coz ur first line of code seems to start from declare is that anonymous block?
regards!

did u missed to insert exception data into tab_ex_err table?

Oh sorry got it in p_pincode procedure, but ia m still confuse with your procedure and function coz it is anonymous, so can;t store in DB.
Can you explain me regarding how it works if it is possible can u change into named procedure and function, sorry to take ur time!
regards!

Actually i hv to store all the procedure and function in DB, so it test whether my function/procedure works or not, i hv to delete table such as tab_ex1, and tab_ex_err. and then run my stored procedure th fill those tables.
I am trying to understand ur code it takes some time for me!

An Anonymous Block can have functions and procedures, just like any other PL/SQL Block (Package/Procedure/Function).

If want you can make it a named block :)

You have asked new questions again in this string of questions :)

The only difference between anonymous block and named block is that you can store the named block in the DB (after compiling it). Whereas anonymous block like I have written will be compiled and executed at run time (each time you run the script).

You wanted me to break my code into functions and procedures, so I did that :)

You probably might have figured it out by now, but here is what the functions and procedure are doing :
1. FUNCTION f_addr_type ( fulladdr varchar2) : Checks which type of address it is. It will basically return "Shop", "Cnr" etc.
2. PROCEDURE p_pincode (i_district IN varchar2, o_postcode OUT varchar2, o_errmsg OUT varchar2) : Is fetching the postcode, and populating errmsg, in case there is some problem with the postcode.
3. FUNCTION f_logerr (tabexrec tab_ex%rowtype, i_errmsg varchar2) : Logs the error in the exceptions table.

Let me know if you have any more questions.

Yha ! got it
function f_addr_type
and procedure p_pincode is named.
but the first line of code starts from
declare
..............

and again later starts with

Begin --main block, is that same block?

so can i turn it into named procedure. I am just confused coz i saw between anonymous blocks there are procedures/functions. Is that the main block to call procedure/function?

Okay i will try to convert it into procedure, do that anonymous bock need any parameters when i turned into named one. or simply i can only give name.?
regards!

Oh now i got it what u did!
ur code starts from anonymous block
declare...

and there are two functions and one procedures are in between them.

i have stored that functions and procedures in my DB using commad CREATE OR REPLACE function <function name>

but if i changed that anonymous main block into named procedure do i need parameter?
 how i like to test is i will delete rows from both table tab_ex1 and tab_ex_err and when i run my main procedure (right now it is anonymous) then it performs the work!

can i create like this:

CREATE OR REPLACE CALL_ALL IS

    v_roadno   varchar2(50);
    v_roadnm   varchar2(200);
    v_lastnum  varchar(100);
    v_tempnm   varchar2(200);
    v_addrtyp  varchar2(30);
    v_postcode varchar2(30);
    v_errmsg   varchar2(250);

    cursor c1 is
    select * from tab_ex;

....
...
end CALL_ALL;

regards!

Just replace the "DECLARE" on the first line to "CREATE OR REPLACE PROCEDURE PROC_TAB_EX AS"

Once this is done, run the SQL script that you have created (with the code that I had given). This would compile the named stored procedure PROC_TAB_EX.

You can then run the procedure by giving command :
exec proc_tab_ex;

You dont need any parameters, because we do everything within the procedure itself.

Okay Got It!
I don;t think now i can ask u more question, coz i am sure its irritating you.
and making bore to you!
regards

Ask more questions if you still are unclear about the above solution. Don't worry. Its not irritating ... :) I enjoy chatting anyways ... and chatting about a solution is great :)

Once you are through with your testing, accept the answer that I gave in post at "Date: 04/05/2004 07:53AM PDT", so that anyone else who wants to benefit from this solution, can read the detailed answer that I gave in that post.

All the best !!

Sure !

but why i am having problem when retrieving data:

like for example i have 300 rows in tab_ex
and after running all procedure/function
i sould only get total rows 300 including on both tables

but when i add rows from two tables its exceeds (more than 300) 300 rows. I think same data are inserted into both tables.

regards

Now it is retriving correct rows, got problem in joins thanx!

Can you please accept the answer and PAQ this question if you are through with your testing.

Thanks!

I hv got some prob!
1. ur previous code used parse this address " 1  A Kalinda Street" as "1A", "kalainda Street" but now it does same at code used to do. It produce  "1" , "A Kalinda Street"
2.same case for "10/1a Frederick Street"
3.Why does it splits this "tom &  jerry Street",  as  "tom &", "jerry Street";
4."10a M'cac street" produce " 10a M' ", "cac Street"

Sorry pratik i definitely accept ur answer, but i dont; want to open new question on same topic previously i did as. So i will like to include points in every question as i did from start of question! (-:

Regards!

3.Why does it splits this "tom &  jerry Street",  as  "tom &", "jerry Street";

It does this parsing in tab_ex1 table in roadname field though it is valid address.

regards!  

Try this now :

--create or replace procedure proc_tab_ex as
declare
    v_roadno   varchar2(50);
    v_roadnm   varchar2(200);
    v_lastnum  varchar(100);
    v_tempnm   varchar2(200);
    v_addrtyp  varchar2(30);
    v_postcode varchar2(30);
    v_errmsg   varchar2(250);

    cursor c1 is
    select * from tab_ex;

    FUNCTION f_addr_type ( fulladdr varchar2)
    RETURN VARCHAR2 IS
     v_space VARCHAR2(30);
    BEGIN
        IF INSTR(fulladdr, 'Cnr') > 0 THEN
            v_space := 'Cnr';
        END IF;
        IF INSTR(fulladdr, 'Shop') > 0 THEN
            v_space := 'Shop';
        END IF;
        IF INSTR(fulladdr, 'PO Box') > 0 THEN
            v_space := 'PO Box';
        END IF;
        IF INSTR(fulladdr, 'Lot') > 0 THEN
            v_space := 'Lot';
        END IF;
        IF INSTR(fulladdr, 'Level') > 0 THEN
            v_space := 'Level';
        END IF;
        IF INSTR(fulladdr, 'Unit') > 0 THEN
            v_space := 'Unit';
        END IF;
        RETURN v_space;
    END f_addr_type;

    PROCEDURE p_pincode (i_district IN varchar2, o_postcode OUT varchar2, o_errmsg OUT varchar2) IS
     v_msg VARCHAR2(250);
    BEGIN
        BEGIN
            SELECT POSTCODE
            INTO   o_postcode
            FROM   tab_pins
            WHERE  DISTRICT = i_district
            AND STATE       = 'West Bengal'
            AND CATEGORY    = 'Courier Service'
            AND ROWNUM      = 1;
        EXCEPTION
        WHEN NO_DATA_FOUND THEN
            o_errmsg:='No Data Found for District in PINCODES table';
        WHEN OTHERS THEN
            o_errmsg:='Others Exception raised for the district';
        END;
    END p_pincode;

    FUNCTION f_logerr (tabexrec tab_ex%rowtype, i_errmsg varchar2)
    RETURN VARCHAR2
    IS
    v_space VARCHAR2(30);
    BEGIN
        insert into tab_ex_err
        (ID, NAME, COMP_NAME, FLD1, DISTRICT, ERRMSG)
        values
        (tabexrec.id, tabexrec.NAME, tabexrec.COMP_NAME, tabexrec.FLD1, tabexrec.DISTRICT, i_errmsg);
        RETURN v_space;
    END f_logerr;


BEGIN -- Main Block
    for c1_rec in c1
    loop
        -- First check whether the District is valid (the one that we are looking for)
        IF c1_rec.DISTRICT IS NULL THEN
            v_errmsg := 'District is not provided';
            v_errmsg := f_logerr(c1_rec,v_errmsg);
            goto loop_next;
        ELSE
            p_pincode(c1_rec.district, v_postcode, v_errmsg);
            IF v_errmsg IS NOT NULL THEN
                v_errmsg := f_logerr(c1_rec,v_errmsg);
                goto loop_next;
            END IF;
        END IF;

        -- Check the format of the address
        v_addrtyp := f_addr_type (c1_rec.fld1);

        -- Case 1. "Cnr"
        --         ignore everything before "Cnr" and put the rest in the house/road name
        IF v_addrtyp = 'Cnr' THEN
            v_roadno := '';
            v_roadnm := substr(c1_rec.fld1, INSTR(c1_rec.fld1, 'Cnr')+ 4);
            goto proc_next;
        END IF;

        -- Case 2. "Shop" -- Case 4. "Lot" -- Case 5. "Level" -- Case 6. "Unit"
        IF v_addrtyp IN ('Shop','Lot','Level','Unit','House') THEN
            -- Find the last number in the string
            v_lastnum := substr(trim(translate(upper(c1_rec.fld1),
                         '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ', '0123456789')),-1);
            v_roadno  := substr(c1_rec.fld1,1,INSTR(c1_rec.fld1,v_lastnum,-1));
            v_roadnm := substr(c1_rec.fld1, INSTR(c1_rec.fld1,v_lastnum,-1)+1);
            goto proc_next;
        END IF;

        -- Case 3. "PO Box"
        --         Put everything in road name
        IF INSTR(c1_rec.fld1, 'PO Box') > 0 THEN
            v_roadno := '';
            v_roadnm := c1_rec.fld1;
            goto proc_next;
        END IF;

        -- If none of the above cases, then its the normal address.
        -- Find the last number in the string
        -- If none of the above cases, then its the normal address.
        v_roadno := substr(c1_rec.fld1, 1, instr(c1_rec.fld1, ' '));
        v_roadnm := substr(c1_rec.fld1, instr(c1_rec.fld1, ' '));

        v_lastnum := substr(trim(translate(upper(v_roadno),
                     '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ', '0123456789')),-1);

        IF v_lastnum is null THEN
             v_roadno := NULL;
             v_roadnm := c1_rec.fld1;
        END IF;

/*
        v_lastnum := substr(trim(translate(upper(c1_rec.fld1),
                     '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ', '0123456789')),-1);
        v_roadno  := substr(c1_rec.fld1,1,INSTR(c1_rec.fld1,v_lastnum,-1));
        v_roadnm := substr(c1_rec.fld1, INSTR(c1_rec.fld1,v_lastnum,-1)+1);
*/

        IF v_roadno IS NULL AND v_roadnm IS NULL THEN
            v_roadnm := c1_rec.fld1;
        END IF;

        <<proc_next>>
        v_roadno := trim(v_roadno);
        v_roadnm := trim(v_roadnm);
        -- last thing, check if the last character of road number is '/' or '?'. Strip if this is the case
        IF substr(v_roadno,-1) = '/' OR substr(v_roadno,-1) = '?' THEN
            v_roadno := substr(v_roadno,1,length(v_roadno)-1);
        END IF;

        IF instr(v_roadnm,' ') > 0 THEN
            -- If road name has more than one word then check if it has
            -- one of the valid values - Rd, Road, Avenue, Street, Pde or PO BOX
            IF instr(v_roadnm, 'Rd') > 0
            OR instr(v_roadnm, 'Road') > 0
            OR instr(v_roadnm, 'Avenue') > 0
            OR instr(v_roadnm, 'Ave') > 0
            OR instr(v_roadnm, 'Street') > 0
            OR instr(v_roadnm, 'Pde') > 0
            OR instr(v_roadnm, 'PO Box') > 0
            OR instr(v_roadnm, 'Drive') > 0
            OR instr(v_roadnm, 'Hwy') > 0
            THEN -- THIS IS A VALID RECORD. Insert into TAB_EX1

                -- If Road Number is null and company name does not exist (i.e., a personal address) then its invalid
                IF v_roadno IS NULL and c1_rec.comp_name IS NULL AND INSTR(v_roadnm, 'PO Box') <= 0 THEN
                    dbms_output.put_line(v_roadno || chr(9) || chr(9) || '|' || chr(9) || v_roadnm || '****Problem');
                    v_errmsg := 'Road Number not provided for personal address';
                    v_errmsg := f_logerr(c1_rec,v_errmsg);
                    goto loop_next;
                END IF;

                dbms_output.put_line(v_roadno || chr(9) || chr(9) || '|' || chr(9) || v_roadnm);

                INSERT INTO tab_ex1
                (ID, NAME, COMP_NAME, ROADNUM, ROADNAME, POSTCODE)
                VALUES
                (c1_rec.ID, c1_rec.NAME, c1_rec.COMP_NAME, v_roadno, v_roadnm, v_postcode);

            ELSE
                dbms_output.put_line(v_roadno || chr(9) || chr(9) || '|' || chr(9) || v_roadnm || '****Problem');
                v_errmsg := 'Invalid Road Name';
                v_errmsg := f_logerr(c1_rec,v_errmsg);
            END IF;
        ELSE
            dbms_output.put_line(v_roadno || chr(9) || chr(9) || '|' || chr(9) || v_roadnm || '****Problem');
            v_errmsg := 'Invalid address format';
            v_errmsg := f_logerr(c1_rec,v_errmsg);
        END IF;

        <<err_rec>>
        NULL;

        <<loop_next>>
        NULL;
    end loop;
end;
/

Hi is it  possible to put exceptional handler in above four procedure and functions?

Yha in one procedure p_pincode u have but i would like to have in others too

This post have become real big now. It takes time to scroll down as well. Since your original question has been answered, and a lot of new questions have also been answered, I suggest you accept the answer in this question, and open a new question if you want.

As for the exception handling, you also have noticed that the exception is handled in the procedure p_pincode. I am not sure which exceptions you expect and wish to trap in others. I had put in the exception handling in procedure because it was required there. In the functions, you can put in exception handling as per own requirements.

It should be a fairly simple thing to do.

Thanks !!  

OK

But the code u have given still does not separate field correclty

1. ur previous code used parse this address " 1  A Kalinda Street" as "1A", "kalainda Street" but now it does same at code used to do. It produce  "1" , "A Kalinda Street"
2.same case for "10/1a Frederick Street"
3.Why does it splits this "tom &  jerry Street",  as  "tom &", "jerry Street";
4."10a M'cac street" produce " 10a M' ", "cac Street"

like this example!

I had mentioned that "1 A Kalinda Street" would always be treated as "1" "A Kalinda Street". Refer to my post dated "Date: 03/31/2004 04:05PM PST". In all the code solutions that I have provided, you would find that "1 A Kalinda Street" is treated as "1" "A Kalinda Street". In case it would have been "1A Kalinda Street", it would have been treated as "1A" "Kalinda Street"

Cases for 10/1a Fredeick Street, Tom & Jerry Street, and 10a M'cac street should now be working in the latest code that I have provided .. dated "Date: 04/07/2004 02:48PM PDT"

There was a glitch in my last-to-latest solution, where I had deleted the lines that used to take care of Normal Addresses like "10/1a Kalinda Street". This problem has been resolved in my latest code that I had put in my post dated "Date: 04/07/2004 02:48PM PDT"

Hi Khushi, Do you still need any help on this ? Is everything working as per your expectations now ?

Waiting to hear !!

Happy Easter to you!!