Separate city name from address sql query

Question

I want to separate city name from the addresses in the Data base.

addresses are in different formats e.g

835 street no.3  Jalabad D.I. Khan ................ here city name is D.I khan
Ho. No. 102 St. No. 85  RawalPindi   ..........here city name is RawalPindi
h no.944 St. No.74  Karkhana road Gujrat   ......here city name is Gujrat
Ho. no.241 S No.26  I-8/3 Isb              .........here city name is isb

I am doing this but it is only working for few... I need a general query which can work for every format of address

SELECT DISTINCT REVERSE
( 
  LEFT( REVERSE(All_Students.Address), CHARINDEX(' ', REVERSE(All_Students.Address))-1 ) 
) as Addresses
from All_Students
order by addresses

Is the format like last space character to the end of the word is the city.? — Ajith Sasidharan, Dec 11 '12 at 12:03
You should store all info in different table fiels [i.e. street, city, zipcode...]. — moonwave99, Dec 11 '12 at 12:03
Storing addresses in a single field is a big-no in SQL and a perfect anti-example of database normalization. — Philipp, Dec 11 '12 at 12:56
I added the SQL Server tag because this seems to be a SQL server query (`charindex`). — Gordon Linoff, Dec 11 '12 at 15:14
What makes a city in your strings? There is no dilimiter that you can use to split it out. — Greg, Dec 12 '12 at 05:21

valex · Accepted Answer · 2012-12-14T12:09:57.763

2

What do we know about Cities in these address strings? City is in the end of the string but it can contain more than 1 word. So I think there is no way to formalize a method to cut a City name from the Address line using only this table.

I think the one way to do it is to find an universal City table for your area/country in the Internet for example in any format (ZIP codes, government statistics,...) and use this table to cut Cities from this table from the Address line.

For MySQL

SELECT TRIM(TRIM(TRAILING Cities.Name FROM Address) ), Cities.Name
from All_Students left join Cities 
     on All_Students.Address like CONCAT('% ',Cities.Name)

For MS SQL Server

SELECT LEFT(Address,LEN(Address)-LEN(Cities.Name)), Cities.Name
from All_Students left join Cities 
     on All_Students.Address like '% '+Cities.Name

To update in MS SQL as required in the comment use this :

UPDATE
    A
SET
    A.address = B.NewAddress,
    A.City = B.City
FROM
    Transformed_All_Student A
    JOIN
    (
      SELECT Student_id, 
             LEFT(Address,LEN(Address)-LEN(Cities.Name)) as NewAddress, 
             Cities.Name as City
      from All_Students left join Cities 
         on All_Students.Address like '% '+Cities.Name

    ) B 
ON A.Student_ID = B.Student_id

edited Dec 14 '12 at 12:09

answered Dec 11 '12 at 12:56

valex

23,966
7
43
60

SELECT TRIM(TRIM(TRAILING Cities_lookup.Dirty_City_Name FROM Address) ), Cities_lookup.Dirty_City_Name from Student left join Cities_lookup on Student.Address like CONCAT('% ',Cities_lookup.Dirty_City_Name) but it is not working Cities_lookup is the name of my lookup table having all the possiblies of city names. it is also giving error on % – ssbb Dec 11 '12 at 17:54
1

I've added MSSQL version of this query. Please use it. – valex Dec 12 '12 at 05:16
how to use these results to update in another table's columns i am doing this but its not working update Transformed_All_Student SET Address= ( SELECT LEFT(Address,LEN(Address)-LEN(g.Dirty_City_name)) from All_Students p left join Cities_lookup g on p.Address like '% '+g.Dirty_City_name WHERE p.Address IS NULL ) where Address is null – ssbb Dec 14 '12 at 10:59
1

How `Transformed_All_Student` table is related to `All_Students`. Is there any common ID? – valex Dec 14 '12 at 11:10
Student_ID is common column – ssbb Dec 14 '12 at 11:28
1

I've added an UPDATE statement to my answer. If this answer worked for you, please [mark it as accepted](http://stackoverflow.com/faq#howtoask) – valex Dec 14 '12 at 11:38
and id i want to update 2 columns?? i am having a Cities table in which one column is Name....(which i am extracting from address in above code) and other column is Standard_Name..... now i want to cut city name from address and place the address in address table as you did it...and also the standard city name against extracted city name from the address in the transformed_All_Student table – ssbb Dec 14 '12 at 11:59
address is going in the table but city name does not – ssbb Dec 15 '12 at 05:43

Separate city name from address sql query

1 Answers1