Hi All,
I have a table(@Address table) with address field. I would like to extract the city name , state and zip code from it.
I have used STRING_AGG() and STRING_SPLIT() to get the City name, state, zip. In these 3 fields City name is very important to show.
Below is the sample code I am using to get the city, state, & zip but it works fine for city names with space in between them but doesn't work for single word states(top 4 works for states with space but not for states with single word, it pulls other street info).
Also I tried with a table to filter the data based on city names from the table but don't know how to use it correctly with string_split()
Any kind of help is greatly appreciate. I am using 2017 version.
declare @Output as varchar(100) = '100 NORTH MAIN MASON CITY IL 626641104'
SELECT @output = STRING_AGG(cityname, SPACE(1)) FROM (SELECT top 4 ROW_NUMBER() Over (order by (select null)) rn, value FROM STRING_SPLIT(@Output, SPACE(1))
order by rn desc) AS cn (rn,cityname);
SELECT @output = STRING_AGG(cityname, SPACE(1)) FROM (SELECT top 4 ROW_NUMBER() Over (order by (select null)) rn, value FROM STRING_SPLIT(@output, SPACE(1))
order by rn asc) AS Cn (rn,cityname);
SELECT @output = STRING_AGG(cityname, SPACE(1)) FROM (SELECT top 4 ROW_NUMBER() Over (order by (select null)) rn, value FROM STRING_SPLIT(@output, SPACE(1))
/ WHERE value IN (SELECT cityname FROM @City ) / order by rn desc ) AS Cn (rn,cityname);
select @output
Below is the sample city table data I want to use it to filter the STRING_Split() data
declare @City table (CityName varchar(50))
insert into @City (CityName)
Select 'PLAIN CITY' union all
Select 'MASON CITY' union all
select 'BLOOMINGDALE' union all
select 'CHICAGO'
--select * from @City
declare @Address table (FullAddress varchar(150))
insert into @Address (FullAddress)
Select '5500 North AVENUE, APT. 116 plain city OH 60053' union all
Select '12536 S INDIANA AVE CHICAGO IL 60625' union all
Select 'W MAIN ST PO BOX 111 Addison VA 24293' union all
select '179 ARMY TRAIL RD BLOOMINGDALE IL 60108' union all
select '100 NORTH MAIN MASON CITY IL 626641104'
--select * from @Address
Thanks in advance
RH