Billy Pang
tuxedo_man at hotmail.com
Sat Oct 30 04:40:21 CDT 2004
Hi Marty:
Thanks for your input. I would like to keep the solution in TSQL. I
glanced over the code posted below and I think that it would not give me the
sorting solution because a2a would not come before a10 and a21 would not go
after a10x (this is the case because "numeric" comes before "alpha", as
defined in the very beginning of the posted code). It is interesting
though...
Billy
>From: MartyConnelly <martyconnelly at shaw.ca>
>Reply-To: dba-sqlserver at databaseadvisors.com
>To: dba-sqlserver at databaseadvisors.com
>Subject: Re: [dba-SQLServer] sorting problem
>Date: Fri, 29 Oct 2004 21:37:05 -0700
>
>I am willing to be corrected about this but you can use this method to hash
>the index field
>and change the ascii collating sequence.
>
>The easy way <grin> to handle this is to use a hash function that creates
>a new index field that is a hash of the alphnumeric characters that
>appears as a double or series of fields converted to doubles. This method
>of hashing is called Base 37 Hash. I have misplaced my written
>documentation on this. On a Zip drive somewhere. Numerical Methods text
>book might have documentation.
>
>I wrote this originally to sort on a field that was old Radio Call Sign
>numbers that looked like 2AAW32 2ABW320 2AaW3223 etc.
>
>Option Compare Database
>Option Explicit
>Const hashFactor As Integer = 37
>Const hashLength As Integer = 9
>
>Function HashString(strHash As String) As Double
>' Create Hash string for indexing using
>'Base 37 Hash Value
>' Convert
>' spaces punctuation odd chars = 0
>' numeric = 0 - 9 1-10
>' alpha chars a-z A-Z 11-37
>' only use lower case
>'such that string "Ab-12" =
>' A b - 1 2
>' (11*37^4) + (12*37^3) + (0*37^2) + (2*37^1) + (3*37^0)
>'The Hash Length is 9 so it fits a double without precision loss
>
>Dim iStrLen As Integer
>Dim decAsc As Double
>Dim i As Integer
>Dim strPad As Integer
>Dim strToHash As String
>HashString = 0
>
>' convert to all lower case
>strToHash = UCase(strHash)
>iStrLen = Len(strToHash)
>
>'pad out string to 9 chars with blanks
>If iStrLen < hashLength Then
>For strPad = (iStrLen + 1) To hashLength
> strToHash = strToHash & " "
>Next
>Else
>' or just grab first nine chars of string
> If iStrLen > hashLength Then
> strToHash = Left(strToHash, hashLength)
> End If
>End If
>
>For i = 1 To hashLength
> decAsc = Asc(Right(strToHash, i))
> 'convert all odd Ascii character values and punctuation to 0
> If (decAsc < 48) Or (decAsc >= 58) And (decAsc <= 64) _
> Or (decAsc > 91) Then
> decAsc = 0
> Else
> 'numbers
> If (decAsc >= 48) And (decAsc <= 57) Then
> decAsc = decAsc - 47
> Else
> 'letters
> If (decAsc >= 65) And (decAsc <= 91) Then
> decAsc = decAsc - 54
> ' 54 not 64 as want to start "A" as 11
> End If
> End If
> End If
>
> HashString = HashString + (decAsc * hashFactor ^ (i - 1))
>Next
>
>End Function
>
>OK now how to call this and use it
>Assume you have a table named [legalnumbering] and two fields
>one called [LegalNumber] containing your 1.1, 1.0.1 numbers; the other
>field that you have added as a Double called [HashTitle1]. This will read
>your 1.0.1 numbered field and create a hash number in the new double field.
>Then just run this routine below and then a query sorting on the hash field
>in the table and displaying your legal number field in correct order.
>
>
>Function HashUpdateHierarchLegalNumber()
>'Update Method Example
>'This example demonstrates the Update method in conjunction with Edit
>method. Updates the hash field from original index field in the original
>table.
>
>
> Dim dbs As Database
> Dim rst As Recordset
> Dim String1 As String
> Dim String2 As String
> ' Set dbs = OpenDatabase("c:\access2\hashing\HashStringA.mdb")
> Set dbs = CurrentDb()
> Set rst = dbs.OpenRecordset("LegalNumbering")
>
> With rst
> Do While Not .EOF
> .Edit
> String1 = Left(![LegalNumber], 9)
> 'String2 = Mid(![TubeNumbers], 10, 9)'chunk out further
> 'String2 = Right(![TubeNumbers], 9) 'if number longer than 9
>digits
> !HashTitle1 = HashString(String1)
> '!HashTitle2 = HashString(String2)
>
> .Update
> .MoveNext
> Loop
> End With
> rst.Close
> dbs.Close
>
>End Function
>
>Not sure but may have stolen this code from part of an article in 1998
>Office Access VB mag article by Jim Inscore on Documentation Sets
>www.advisor.com
>
>
>
>
>Mark Rider wrote:
>
>>On Sat, 30 Oct 2004 00:35:23 +0000, Billy Pang <tuxedo_man at hotmail.com>
>>wrote:
>>
>>
>>>Hello:
>>>
>>>I have a table that contains one column with the following six values:
>>>
>>>a1
>>>a2
>>>a21
>>>a10x
>>>a2a
>>>a10
>>>
>>>Currently if that column is sorted, it is sorted this way:
>>>
>>>a1
>>>a10
>>>a10x
>>>a2
>>>a21
>>>a2a
>>>
>>>But I want it to be sorted this way:
>>>
>>>a1
>>>a2
>>>a2a
>>>a10
>>>a10x
>>>a21
>>>
>>>
>>
>>What are you using to view the sorted column? That will be the best
>>thing to use to figure out the sort. Pull the data from the column,
>>and have the program sort it as you want.If you are looking at the
>>column in EM then it will sort according to how the database sees it,
>>and that is the way the text will be sorted.
>>
>>
>>
>
>--
>Marty Connelly
>Victoria, B.C.
>Canada
>
>
>
>_______________________________________________
>dba-SQLServer mailing list
>dba-SQLServer at databaseadvisors.com
>http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
>http://www.databaseadvisors.com
>