Articles   Members Online:
-Article/Tip Search
-News Group Search over 21 Million news group articles.
-Delphi/Pascal
-CBuilder/C++
-C#Builder/C#
-JBuilder/Java
-Kylix
Member Area
-Home
-Account Center
-Top 10 NEW!!
-Submit Article/Tip
-Forums Upgraded!!
-My Articles
-Edit Information
-Login/Logout
-Become a Member
-Why sign up!
-Newsletter
-Chat Online!
-Indexes NEW!!
Employment
-Build your resume
-Find a job
-Post a job
-Resume Search
Contacts
-Contacts
-Feedbacks
-Link to us
-Privacy/Disclaimer
Embarcadero
Visit Embarcadero
Embarcadero Community
JEDI
Links
How to use a more accurate sort order in MSSQL7 Turn on/off line numbers in source code. Switch to Orginial background IDE or DSP color Comment or reply to this aritlce/tip for discussion. Bookmark this article to my favorite article(s). Print this article
25-Aug-03
Category
Database-SQL
Language
Delphi 2.x
Views
127
User Rating
No Votes
# Votes
0
Replies
0
Publisher:
DSP, Administrator
Reference URL:
DKB
			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 
--- 

			
Vote: How useful do you find this Article/Tip?
Bad Excellent
1 2 3 4 5 6 7 8 9 10

 

Advertisement
Share this page
Advertisement
Download from Google

Copyright © Mendozi Enterprises LLC