How to get just the city name from an address?

Transact-SQL
1

Hello everyone! Here's my problem.

I have three columns: Company, Address, Subcategory

Datasets under 'Address' look like these
'66, ABC Street, Random City, Metro Manila'
'11 Something City, Brgy Parang, 1809 Metro Manila'
'0106, Calan District, Another City, Pangasinan'

How do I extract just City Name (ex: Random City) from the 'Address' column so that my query results are in this format: 'Company', 'City', 'Subcategory'

I'm not really sure how to go about this?

Any help will be greatly appreciated!

2

Do every single city name and with the word City? Otherwise the data is so inconsistent

1 Like
3

To be able to parse a string like this - the string must be formatted consistently. If the format isn't consistent then you have no way of knowing what element or position in the string to extract.

For a string with this format nnnn, street name, city name, area you can parse out the 3rd element of the string. For a string with this format nnnn city, country, area you can parse the second space element of the first comma element.

Before you can even begin to code - you need to identify all of the valid formats and figure out how to identify that format. For example, what if you also have a format of street, city, state - how would you identify that as different from nnnn city, country, area?

Once you have identified all of the valid formats - and a method of determine each format, then you can use one of several different methods to parse. You can use charindex/substring or json_value (2016 and higher).

2 Likes
4

Or a master list of city names? Along with delimitedsplit8k to breakdown OP list?

1 Like
5 
use sqlteam
go

declare @fluffy table(cityjunkdata nvarchar(150))

insert into @fluffy
select '66, ABC Street, Random City, Metro Manila' union
select '11 Something City, Brgy Parang, 1809 Metro Manila' union
select '0106, Calan District, Another City, Pangasinan'


select c.Item
from @fluffy f
cross apply DelimitedSplit8K(f.cityjunkdata, ',') c
where c.Item like '%city%'

but like @jeffw8713 said this is an exercise in futility unless you provide us some more details he mentions

1 Like
6

Won't work very well, you would need every possible derivation as well as all abbreviations.

1 Like
7

Yeah unfortunately the data I have isn't formatted consistently. Thanks for the tip though!

8

Thanks for the suggestion!

9

the question is how would you identify

Its a City

One Idea .... do a google search .. see the result .. if it says city ( YES )