Mega Code Archive
Towards a more accurate sort order in MSSQL7
<![CDATA[
Title: Towards a more accurate sort order in MSSQL7
Question: 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
This is a T-SQL version of article 2982
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 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 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 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
---
]]>