Author: Massimo Brini 

Sorting Addresses is a pain at the best of times, especially when a client supplies 
bad data (You may define clear fields in your DB, but when the data comes in, does 
it fit easily??) 
This attempts to resolve this issue for MSSQL Server 

Answer:

Wherever you keep the addresses, add a field SortOrder (real) 
Whenever the Address changes, update the new value using this stored procedure to 
calculate the value. 

Using the server to do the work will cut out network traffic, etc. 

It can be called to update using something like this. 

--- 
DECLARE @Addr varchar(100),@SortIndex real 

SET @Addr=(SELECT ISNULL(Addr1+' ','')+ISNULL(Addr2+' ','')+ISNULL(Addr3+' 
','')+ISNULL(Addr4+' ','')+ISNULL(Addr5+' ','')+ISNULL(PCode,'') FROM Main WHERE 
ID=@Main_ID) 

EXEC spCalcSortIndex @Addr,@Index=@SortIndex OUTPUT 

UPDATE Main 
SET SortIndex=@SortIndex 
WHERE ID=@Main_ID 
--- 

Here is the Complete Stored Procedure to copy and paste in: 

--- 
Create Procedure "spCalcSortIndex" @NumStr varchar(100)='',@Index real OUTPUT 
AS 

/*This will return a sort index based on the @NumStr passed 
Call as: DECLARE @Value_I_Want real 
EXEC spCalcSortIndex (SELECT AddressFields FROM Addresses WHERE 
ID=x),@Index=@Value_I_Want OUTPUT*/ 

DECLARE @strlen int,@i int,@j int 
DECLARE @found bit 
DECLARE @numpart real,@strpart real, @divisor real 
DECLARE @ChoppedStr varchar(100) 

SET @strlen=LEN(@NumStr) 

IF @strlen=0 
BEGIN 
SET @Index=0 
RETURN 
END 

/*Split the string into a 'number' and a 'string' part*/ 

/*Initialise*/ 
SELECT @found=0, @ChoppedStr=@NumStr,@numpart=0,@i=1 

/*Locate the first digit*/ 
WHILE @i<=@strlen 
BEGIN 
IF SUBSTRING(@NumStr,@i,1) IN ('0','1','2','3','4','5','6','7','8','9') 
BEGIN 
SET @found=1 
BREAK 
END 
SET @i=@i+1 
END 

IF @found=1 
BEGIN 
/*now get the remaining digits*/ 
SELECT @found=0,@j=@i 

WHILE @j<=@strlen 
BEGIN 
IF SUBSTRING(@NumStr,@j,1) NOT IN ('0','1','2','3','4','5','6','7','8','9') 
BEGIN 
SET @found=1 
BREAK 
END 
SET @j=@j+1 
END 

/*Separate out the string parts*/ 
IF @found=1 
BEGIN 
/*Number was embedded..*/ 
SELECT @numpart=CONVERT(real,SUBSTRING(@NumStr,@i,@j-@i)), 
@ChoppedStr=LEFT(@Numstr,@i-1)+RIGHT(@NumStr,@strlen-@j+1) 
END 
ELSE 
BEGIN 
/*Number went to the end of the string*/ 
SELECT @numpart=CONVERT(real,SUBSTRING(@NumStr,@i,@strlen)), 
@ChoppedStr=LEFT(@Numstr,@i-1) 
END 
END 

SET @Choppedstr=UPPER(LTRIM(RTRIM(@ChoppedStr))) 
SET @strlen=LEN(@ChoppedStr) 

/*Evaluate a Number for the remaining part of the string*/ 
SELECT @strpart=0,@divisor=1,@i=1 

WHILE @i<=@strlen 
BEGIN 
SET @divisor=@divisor/256 
SET @strpart=@strpart+(ASCII(SUBSTRING(@ChoppedStr,@i,1))*@divisor) 
SET @i=@i+1 
END 

/*All done, return the value*/ 
SET @Index=@numpart+@strpart 
---