Consulting @ Gregory Lane

Sorting Street Addresses with T-SQL

by Gary Randolph

Take the following street addresses:

622 E. 8th St.
2512 E. 8th St.
2329 W. 8th St.
905 E. 27th St.
1325 E. 27th St.
1102 Arrow Ave.
1201 Arrow Ave.
2724 Central Ave.

For most people these addresses are in the order they would expect.

  • The numbered streets appear before the named streets.
  • The numbered streets appear in number order.
  • Addresses on the same street are grouped by East, West, North, South if those designations exist.
  • House numbers are in numeric order for a given street (within the E, W, N, S groupings).

If your database has just a single Street Address field, then it can be tricky to produce such a sorted list. Here is a solution for SQL-Server using T-SQL functions. I'm not saying this is the most elegant solution possible. But it works great for my target database; it can sort the 363 addressesof a client's rental management system in 0 seconds.

To make it work, I wrote three functions:

The first function, fHouseNum returns just the house number. It assumes the house number appears at the beginning of the street address. It uses a While loop to evaluate each successive character in the address. If the character is a numeric digit, then it appends it to the @HouseNum variable. When it finds a non-numeric digit. it quit

CREATE FUNCTION dbo.fHouseNum
(
@Src nvarchar(255)
)
RETURNS int
AS
BEGIN
declare @HouseNum nvarchar(255)
declare @i int, @l int, @c char
select @i=1, @l=len(@Src)
SET @HouseNum = ''
while @i<=@l
begin
   set @c=upper(substring(@Src,@i,1))
    IF  @c IN ('0','1','2','3','4','5','6','7','8','9')
        Begin
        SET @HouseNum = @HouseNum + @c
        set @i=@i+1
        End
    Else
        Break
end
return(Cast(@HouseNum as int))
END
GO

The scond function, fStreetDir, returns just the street name followed by the direction (N,S, E, W). So for the address "622 E. 8th St." it would return "8th St. E," which is how we want to group the addresses. The function identifies the spaces in the address to split apart: the house number, the street direction (if any), and the street name. Note the special logic to make sure that what it thinks is a street direction is followed by a space or a period. That could cause problems if the entry is written as "203 West 11th." That is not the case in my client's database. But if it were, we could add logic to spot the written out direction names (north, south, east, west).

Create Function dbo.fStreetDir (@Src varchar(50))
RETURNS varchar(150)
AS
BEGIN
declare @streetdir varchar(150)
declare @firstSpace int
declare @secondSpace int
declare @houseNum varchar(50)
declare @dir varchar(50)
declare @street varchar(50)

Set @Src = RTrim(LTrim(@Src))
Select @firstSpace = CharIndex(' ',@Src,0)
Select @secondSpace = CharIndex(' ',@Src,@firstSpace+1)
Select @houseNum = Substring(@Src,0,@firstSpace)
If @secondSpace>0 And (Substring(@Src,@firstSpace+2,1)=' ' or Substring(@Src,@firstSpace+2,1)='.')    -- direction followed by space or .
    Begin
    -- has direction designation, e.g. 203 W. 11th St.
    Select @dir = Substring(@Src,@firstSpace+1,1)
    Select @street = Substring(@Src,@secondSpace+1,Len(@Src)-@secondSpace)
    End
Else
    Begin
    -- no direction designation, e.g. 2114 Columbus Av
    Set @dir=''
    Select @street = Substring(@Src,@firstSpace+1,Len(@Src)-@firstSpace)
    --Set @street=''
    End
Set @streetdir = @street + ' ' + @dir
return(@streetdir)
END
GO

 The third function, fStreetAsNum, is largely redundant with fStreetDir, but is needed to let us sort the numbered streets in numeric order. Otherwise, the streets would be ordered as 1st, 10th, 11th, 12th... and placing 2nd street only after 19th street (or 1xxth). This function returns a number for numbered streets (e.g. 8 for 8th street) and 9999 for named streets. When we sort by that, it will put all numbered streets in order before all named streets.

Create Function dbo.fStreetAsNum (@Src varchar(50))
RETURNS int
AS
BEGIN
declare @StreetNum varchar(150)
declare @firstSpace int
declare @secondSpace int
declare @houseNum varchar(50)
declare @street varchar(50)
declare @i int, @l int, @c char

Set @Src = RTrim(LTrim(@Src))
Select @firstSpace = CharIndex(' ',@Src,0)
Select @secondSpace = CharIndex(' ',@Src,@firstSpace+1)
Select @houseNum = Substring(@Src,0,@firstSpace)
If @secondSpace>0 And (Substring(@Src,@firstSpace+2,1)=' ' or Substring(@Src,@firstSpace+2,1)='.')    -- direction followed by space or .
    Begin
    -- has direction designation, e.g. 203 W. 11th St.
    Select @street = Substring(@Src,@secondSpace+1,Len(@Src)-@secondSpace)
    End
Else
    Begin
    -- no direction designation, e.g. 2114 Columbus Av
    Select @street = Substring(@Src,@firstSpace+1,Len(@Src)-@firstSpace)
    --Set @street=''
    End
select @i=1, @l=len(@street)
SET @StreetNum = ''
while @i<=@l
begin
   set @c=upper(substring(@street,@i,1))
    IF  @c IN ('0','1','2','3','4','5','6','7','8','9')
        Begin
        SET @StreetNum = @StreetNum + @c
        set @i=@i+1
        End
    Else
        Break
end
If @StreetNum=''
    Set @StreetNum='9999'
return(Cast(@StreetNum as int))
ENDGO

Finally we just have to use these functions in the ORDER BY clause of our SQL This will do the trick:

Select Address
From Property
Order by dbo.fStreetAsNum(Address), dbo.fStreetDir(Address), dbo.fHouseNum(Address)

Here's what each function returns using our sample street addresses:

Adddress fStreetDir fHouseNum fStreetAsNum
622 E. 8th St. 8th St. E 622 8
2512 E. 8th St. 8th St. E 2512 8
2329 W. 8th St. 8th St. W 2329 8
905 E. 27th St. 27th St. E 905 27
1325 E. 27th St. 27th St. E 1325 27
1102 Arrow Ave. Arrow Ave. 1102 9999
1201 Arrow Ave. Arrow Ave. 1201 9999
2724 Central Ave. Central Ave. 2724 9999

That's about it. Good luck.

Copyright © 2010

Follow me on Twitter

Gary Randolph
2304 Gregory Lane
Anderson, IN 46012
Phone: 765.683.0309
E-mail: gr@gregorylane.com
Contact Us