[dba-SQLServer] sorting problem

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