Find a city in the middle of a field

[RUDOLFSUN]

Ok Im having trouble with this, I need to remove cities from an address field but don't know a simple query that does this. Can any body help

heres an example

Address1 City
10/F, HONG KONG LAI CHI KOK EXCHANGE II HONG KONG

Now I want to be able to pull Hong kong out of the address1 field but without searching in the address field with *HONG KONG* as this would involve checking every city in a table.

Please help!

 

[ajetrumpet]

Rudolf,

You will have to write a function to do this. How many possible city names are there in the table? You are stuck here, because the program doesn't recognize cities. It can't tell you those names, but it can tell you if the table contains the string of characters that allow you to say the city name.

So, you'll have to write something like this:

Public Function RemoveCities(CityName as string) as string

  if CityName LIKE "*" & "FirstCityNameHere" & "*" then
    RemoveCities = "FirstCityName"

  elseif CityName LIKE "*" & SecondCityNameHere" & "*" then
    RemoveCities = "SecondCityName"

  etc, etc...[I]for every possible city name[/I]

  else
    RemoveCities = CityName

  end if

End Function

That will only tell you if there is a city in the field value that is in the list of cities you checked in the function. If I wrote that all out, I would then write an SQL statement to delete the cities from the cell values, leaving the rest of the string as the remaining cell value:

UPDATE table SET
   table.CityField = IIF(RemoveCities(table.CityField) <> table.CityField, _
      Replace(table.CityField, "" & RemoveCities(table.CityField) & "", ""), _
         table.CityField)

The Replace function here is the key. It'll take out the delimited city value, and insert a blank space as a replacement value. It's not perfect, but if you did this, I'm sure you could modify it to fit your needs.

This method is easy if you don't have a million different city name to deal with, AND youf field has consistent delimiting in it. Because, if you are looking for "HONG KONG", and your field value could be any one of these values: HONGKONG, HONG KONG, HONG KONG, HONG.KONG, etc..., then you're going to have a difficult time locating it.

Bottom Line - You're going to have to give the program some way of identifying all of the different possible cities that could be listed in the field string. Now, weather you do that with a subquery (something like WHERE table.CityName EXISTS) or with a function, it doesn't matter, but you still have to do it. This process would be a whole lot easier if you had another place in the database where all of the cities were listed, or all of them were at least present in another table somewhere (even nested inside of other field strings would be useful, as long as the city list was all-inclusive for every possibility you want to look for).