Towards a more accurate sort order in MSSQL7

Problem/Question/Abstract:

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
---