1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Searching city name from address

Discussion in 'SQL PL/SQL' started by Tariq Bashir Malhi, Apr 16, 2010.

  1. Tariq Bashir Malhi

    Tariq Bashir Malhi Forum Advisor

    Messages:
    44
    Likes Received:
    0
    Trophy Points:
    80
    I want to write a SQL statement to search valid city name from address field. Valid city names are in one table and address column is in another table.
     
  2. Sadik

    Sadik Community Moderator Forum Guru

    Messages:
    1,906
    Likes Received:
    252
    Trophy Points:
    1,455
    Please share your create table and some sample insert statements and also what code you have written.
     
  3. Tariq Bashir Malhi

    Tariq Bashir Malhi Forum Advisor

    Messages:
    44
    Likes Received:
    0
    Trophy Points:
    80

    Code (SQL):
     
    CREATE TABLE ip_address (ip_name varchar2(35),
    address varchar2(100), city_code NUMBER(4));

    INSERT INTO ip_address VALUES ('Aslam','e203b defence view Karachi',0);

    INSERT INTO ip_address VALUES ('Tariq','e203b defence view Islamabad',0);

    INSERT INTO ip_address VALUES ('Bashis','e203b defence view Huderabad',0);

    CREATE TABLE city_info (city_name varchar2(30), city_code NUMBER(4));

    INSERT INTO city_info VALUES ('KARACKI',1);

    INSERT INTO city_info VALUES ('ISLAMABAD',2);

    INSERT INTO city_info VALUES ('HYDERABAD',3);
     

    I want to populate city_code in ip_address table from city_info by searching city_info.city_name in ip_address.address column.
     
  4. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    You can try with INSTR function .

    INSTR Function




    Code (SQL):
    UPDATE IP_ADDRESS ADR
      SET CITY_CODE = ( SELECT CTY.CITY_CODE
                          FROM  CITY_INFO CTY
                         WHERE INSTR(UPPER(ADR.ADDRESS), CTY.CITY_NAME, 1 ) >0)
    WHERE EXISTS ( SELECT NULL
                          FROM  CITY_INFO CTY
                         WHERE INSTR(UPPER(ADR.ADDRESS), CTY.CITY_NAME, 1 ) >0)
    Here It updates the city code only when it finds City name in UPPER(Address) (>0). Where Exists is mandatory to update only the matching records.
     
  5. Tariq Bashir Malhi

    Tariq Bashir Malhi Forum Advisor

    Messages:
    44
    Likes Received:
    0
    Trophy Points:
    80
    It is working fine and i am thank full for this. In actual senerio i have 4 million + rows in ip_address table and there are different cases e.g

    1. city name is in the middle or on any other position of address.
    2. city name comprise of two words 'LUCKY MARWATT' .

    Code (SQL):


    INSERT INTO ip_address VALUES ('lIAQUAT','LUCKY MARWATT, AHMED TOWN PHASE 5 ',0);


    INSERT INTO city_info VALUES ('LUCKY MARWATT',4);

     
    What should i do for such cases.
     
  6. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    Same Query will work for all the scenario.

    Code (SQL):
    SQL> SELECT * FROM IP_ADDRESS ;

    IP_NAME                             ADDRESS                              CITY_CODE
    ----------------------------------- ----------------------------------- ----------
    Aslam                               e203b defence VIEW Karachi                   0
    Tariq                               e203b defence VIEW Islamabad                 0
    Bashis                              e203b defence VIEW Huderabad                 0
    lIAQUAT                             LUCKY MARWATT, AHMED TOWN PHASE 5            0

    SQL> SELECT * FROM CITY_INFO;

    CITY_NAME                       CITY_CODE
    ------------------------------ ----------
    KARACKI                                 1
    ISLAMABAD                               2
    HYDERABAD                               3
    LUCKY MARWATT                           4

    SQL> UPDATE IP_ADDRESS ADR
      2    SET CITY_CODE = ( SELECT CTY.CITY_CODE
      3                        FROM  CITY_INFO CTY
      4                       WHERE INSTR(UPPER(ADR.ADDRESS), CTY.CITY_NAME, 1 ) >0)
      5  WHERE EXISTS ( SELECT NULL
      6                        FROM  CITY_INFO CTY
      7                       WHERE INSTR(UPPER(ADR.ADDRESS), CTY.CITY_NAME, 1 ) >0);

    2 ROWS updated.

    SQL> SELECT * FROM IP_ADDRESS ;

    IP_NAME                             ADDRESS                              CITY_CODE
    ----------------------------------- ----------------------------------- ----------
    Aslam                               e203b defence VIEW Karachi                   0
    Tariq                               e203b defence VIEW Islamabad                 2
    Bashis                              e203b defence VIEW Huderabad                 0
    lIAQUAT                             LUCKY MARWATT, AHMED TOWN PHASE 5            4

    SQL>
     
    But In case there are millions of rows to be updated, you may have to go for BULK Updates in PL/SQL . (BULK COLLECT, FORALL). Straight SQL may lead to performance issues in non-optimized databases(like Snapshot error,Undo table space Issues etc) . Then BULK Updates will be better option.

    Then you have to

    1. Take the Junk of 10k-20k records in a stretch.
    2. Update the City Code
    3. Commit.
     
    Tariq Bashir Malhi likes this.