Parse address field to extract City name using t-sql

SQL9 246 Reputation points
2021-05-30T22:24:33.51+00:00

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

Developer technologies | Transact-SQL
Developer technologies | Transact-SQL

A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.

Locked Question. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

Answer accepted by question author
MelissaMa-msft 24,246 Reputation points Moderator
2021-06-02T04:45:16.987+00:00

Hi @SQL9 ,

Thanks for your update.

If you could list all cityname with "City", "Ville" or anything, you could refer below: 

;with cte as (  
select  ROW_NUMBER() Over (order by (select null)) addressid,FullAddress from @Address)  
,cte1 as (  
SELECT ROW_NUMBER() Over (partition by addressid order by (select null) ) id, value,addressid FROM cte  
cross apply STRING_SPLIT(FullAddress, SPACE(1)))  
,cte2 as (  
select ROW_NUMBER() Over (partition by addressid order by id desc) rn ,value ,addressid  
from cte1)  
,cte3 as (  
select a.*,b.value1   
from cte2 a  
left join (select addressid,value value1 from cte2 where rn=3) b on a.addressid=b.addressid)  
select STRING_AGG(value,SPACE(1)) WITHIN GROUP (ORDER BY rn DESC) Result  
from cte3  
where value1 in ('city','Ville') and rn<=4  
group by addressid  
union   
select STRING_AGG(value,SPACE(1)) WITHIN GROUP (ORDER BY rn DESC) Result  
from cte3  
where value1 not in ('city','Ville') and rn<4  
group by addressid

Output: 

Result  
ADDISON VA 24293  
BLOOMINGDALE IL 60108  
CHICAGO IL 60625  
EDISON NJ 34561  
MASON CITY IL 626641104  
PLAIN CITY OH 60053

Or you could refer another method using user-defined function.

Firstly create one function as below: 

CREATE FUNCTION dbo.GetSplitString  
(  
   @List       VARCHAR(MAX),  
   @Delimiter  VARCHAR(255),  
   @ElementNumber int  
)  
RETURNS VARCHAR(4000)  
AS  
BEGIN  
   DECLARE @result varchar(4000)      
   DECLARE @Items TABLE ( position int IDENTITY PRIMARY KEY,  
                          Item VARCHAR(4000)  
                         )    
  
   DECLARE @ll INT = LEN(@List) + 1, @ld INT = LEN(@Delimiter);    
  
   WITH a AS  
   (  
       SELECT  
           [start] = 1,  
           [end]   = COALESCE(NULLIF(CHARINDEX(@Delimiter,   
                       @List, @ld), 0), @ll),  
           [value] = SUBSTRING(@List, 1,   
                     COALESCE(NULLIF(CHARINDEX(@Delimiter,   
                       @List, @ld), 0), @ll) - 1)  
       UNION ALL  
       SELECT  
           [start] = CONVERT(INT, [end]) + @ld,  
           [end]   = COALESCE(NULLIF(CHARINDEX(@Delimiter,   
                       @List, [end] + @ld), 0), @ll),  
           [value] = SUBSTRING(@List, [end] + @ld,   
                     COALESCE(NULLIF(CHARINDEX(@Delimiter,   
                       @List, [end] + @ld), 0), @ll)-[end]-@ld)  
       FROM a  
       WHERE [end] < @ll  
   )  
   INSERT @Items SELECT [value]  
   FROM a  
   WHERE LEN([value]) > 0  
   OPTION (MAXRECURSION 0);  
  
   SELECT @result=Item  
   FROM @Items  
   WHERE position=@ElementNumber  
     
   RETURN @result;  
END  
GO

Then call this function as below: 

SELECT case when REVERSE(dbo.GetSplitString(REVERSE(replace(FullAddress,' ',',')),',',3)) in ('city','Ville')  
then right(FullAddress,charindex(' ', REVERSE(FullAddress),charindex(' ', REVERSE(FullAddress),charindex(' ', REVERSE(FullAddress), (charindex(' ', REVERSE(FullAddress), 1))+1)+1)+1))  
else right(FullAddress,charindex(' ', REVERSE(FullAddress),charindex(' ', REVERSE(FullAddress), (charindex(' ', REVERSE(FullAddress), 1))+1)+1))  
end result  
from @Address

Best regards,
Melissa

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Was this answer helpful?

5 additional answers

Sort by: Newest
Most helpful Newest Oldest
  1. Deleted

    This answer has been deleted due to a violation of our Code of Conduct. The answer was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.

    Comments have been turned off. Learn more

  2. Guoxiong 8,221 Reputation points
    2021-06-01T17:02:17.093+00:00

    Suppose the city name in the Address table can be found in the City table. You can try this:

    ;WITH CTE AS (
    SELECT a.FullAddress, CHARINDEX(c.CityName, a.FullAddress) AS CityStartPosition 
    FROM @Address AS a, @City AS c
    WHERE CHARINDEX(c.CityName, a.FullAddress) > 0
)

SELECT FullAddress, SUBSTRING(FullAddress, CityStartPosition, LEN(FullAddress) - CityStartPosition + 1) AS Result
FROM CTE;

    Was this answer helpful?

  3. Deleted

    This answer has been deleted due to a violation of our Code of Conduct. The answer was manually reported or identified through automated detection before action was taken. Please refer to our Code of Conduct for more information.

    Comments have been turned off. Learn more

  4. MelissaMa-msft 24,246 Reputation points Moderator
    2021-05-31T02:55:07.26+00:00

    Hi @SQL9 ,

    Welcome to Microsoft Q&A!

    According to your situation, you could consider to add one condition of value<>'city'. Then you could only choose the top 3 values.

    Please refer below: 

    declare @Output as varchar(100) = '100 NORTH MAIN MASON CITY IL 626641104'  
SELECT @output = STRING_AGG(cityname, SPACE(1)) FROM (SELECT top 3 ROW_NUMBER() Over (order by (select null)) rn, value FROM STRING_SPLIT(@Output, SPACE(1))  
where value<>'city'  
order by rn desc) AS cn (rn,cityname);  
select @output  
--626641104 IL MASON  
  
declare @Output1 as varchar(100) = '5500 North AVENUE, APT. 116 plain city OH 60053'  
SELECT @output1 = STRING_AGG(cityname, SPACE(1)) FROM (SELECT top 3 ROW_NUMBER() Over (order by (select null)) rn, value FROM STRING_SPLIT(@Output1, SPACE(1))  
where value<>'city'  
order by rn desc) AS cn (rn,cityname);  
select @output1  
--60053 OH plain

    You could also refer below query: 

    ;with cte as (  
select  ROW_NUMBER() Over (order by (select null)) addressid,FullAddress from @Address)  
,cte1 as (  
SELECT ROW_NUMBER() Over (partition by addressid order by (select null) ) id, value,addressid FROM cte  
cross apply STRING_SPLIT(FullAddress, SPACE(1))   
where value<>'city')  
,cte2 as (  
select ROW_NUMBER() Over (partition by addressid order by id desc) rn ,value ,addressid  
from cte1)  
select  STRING_AGG(value,SPACE(1)) AS Result  
from cte2  
where rn<4   
group by addressid

    Output: 

    Result  
60053 OH plain  
60625 IL CHICAGO  
24293 VA Addison  
60108 IL BLOOMINGDALE  
626641104 IL MASON

    Best regards,
    Melissa

    If the answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    Was this answer helpful?

    0 comments