SQL Address Parsing

Question

I have a query where I'm parsing for unit # from some addresses.

CASE WHEN Address1 LIKE '%apt %' THEN SUBSTRING(Address1, CHARINDEX('Apt', Address1), LEN(Address1))

This works as it should when the address is set up as: "123 Main St. Apt. 1A".

I will get "Apt. 1A" as my result.

However, there are some instances where my data is set up differntly, an example of this would be "Apt. 2B 123 Fake Street". When the data is set up like this I get "Apt. 2B 123 Fake Street"

The result I'm looking for is "Apt. 2B"

How do I get my query to get the "Apt." text and all of the following text up to the next space?

Parsing addresses in TSQL doesn't seem a good idea. – Martin Smith Jul 19 '17 at 18:19 — Martin Smith, Jul 19 '17 at 18:19

S3S · Answer 1 · 2017-07-19T19:39:31.023

There's a ton of things that could make this go wrong... and this code probably won't work for all cases depending on the format of your address. Assuming the format is Apt then a space AptNumberLetter then a space... this could work. I broke it up to show what it does. Again, normalizing dates is what you need to do as Martin stated.

declare @table table (addy varchar(450))
insert into @table
values
('Apt. 2B 123 Fake Street'),
('123 Main St. Apt. 1A')

select
    addy
    ,CHARINDEX('Apt',addy)startLocation
    ,CHARINDEX(' ',addy,CHARINDEX('Apt',addy)) firstSpace
    ,CHARINDEX(' ',addy,CHARINDEX(' ',addy,CHARINDEX('Apt',addy)) + 1) endSpace
    ,substring(addy,CHARINDEX('Apt',addy),case when CHARINDEX(' ',addy,CHARINDEX(' ',addy,CHARINDEX('Apt',addy)) + 1) = 0 then 99 else CHARINDEX(' ',addy,CHARINDEX(' ',addy,CHARINDEX('Apt',addy)) + 1) end) FinalResult
from @table

John Cappelletti · Answer 2 · 2017-07-19T19:15:57.300

If you are open to a UDF.

Tiered of parsing and extracting strings (charindex,left,right,...), I modified a parse/split udf to accept two non-like parameters.

Now, parsing addresses can be a slippery-slope. See my answer here Address standardization within a database

Example

Declare @YourTable table (Address1 varchar(450))
Insert into @YourTable values 
 ('Apt. 2B 123 Fake Street')
,('123 Main St. Apt. 1A')
,('100 North Main St.')

Select A.Address1
      ,Apt = 'Apt. '+B.RetVal
 From @YourTable A
 Outer Apply [dbo].[udf-Str-Extract](' '+A.Address1+' ',' Apt. ',' ') B

Returns

Address1                     Apt
Apt. 2B 123 Fake Street      Apt. 2B
123 Main St. Apt. 1A         Apt. 1A
100 North Main St.           NULL

The UDF if Interested

CREATE FUNCTION [dbo].[udf-Str-Extract] (@String varchar(max),@Delimiter1 varchar(100),@Delimiter2 varchar(100))
Returns Table 
As
Return (  

with   cte1(N)   As (Select 1 From (Values(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) N(N)),
       cte2(N)   As (Select Top (IsNull(DataLength(@String),0)) Row_Number() over (Order By (Select NULL)) From (Select N=1 From cte1 N1,cte1 N2,cte1 N3,cte1 N4,cte1 N5,cte1 N6) A ),
       cte3(N)   As (Select 1 Union All Select t.N+DataLength(@Delimiter1) From cte2 t Where Substring(@String,t.N,DataLength(@Delimiter1)) = @Delimiter1),
       cte4(N,L) As (Select S.N,IsNull(NullIf(CharIndex(@Delimiter1,@String,s.N),0)-S.N,8000) From cte3 S)

Select RetSeq = Row_Number() over (Order By N)
      ,RetPos = N
      ,RetVal = left(RetVal,charindex(@Delimiter2,RetVal)-1) 
 From  (
        Select *,RetVal = Substring(@String, N, L) 
         From  cte4
       ) A
 Where charindex(@Delimiter2,RetVal)>1

)
/*
Max Length of String 1MM characters

Declare @String varchar(max) = 'Dear [[FirstName]] [[LastName]], ...'
Select * From [dbo].[udf-Str-Extract] (@String,'[[',']]')
*/

Is there a code golf version of this somewhere? (Remind me never to go up against you in a SQL coding event) — xQbert, Jul 19 '17 at 19:22
@xQbert I'd partner-up with you any day. Just a lazy coder. I'd rather write one function and use in numerous times. — John Cappelletti, Jul 19 '17 at 19:25
@scsimon Honestly, I like this one. I've used it for parsing JSON, XML, HTML, and numerous miscellanous others. — John Cappelletti, Jul 19 '17 at 19:35

SQL Address Parsing

2 Answers2