• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 661
  • Last Modified:

function to convert 13 digit numeric field to 7 or fewer alphanumeric

I have a table of 13 digit numbers, all unique.  I have a requirement to fill a pre-existing field that can only hold 7 characters.  Is anyone aware of an algorithm that I might use get the 13 digit number to hash down to 7 alphanum?

Thank you experts!!!
0
tonyraymond
Asked:
tonyraymond
  • 9
  • 6
  • 6
  • +2
1 Solution
 
NevaarCommented:
If you use capital letters and numbers, with seven characters you can only get 2.5 billion unique numbers (which isn't even a full 10 digits)

Does your 13 digit table contain over 2.5 billion records?  Or is the field just that big?  Does it need to be an algorithm or can this be a start at A000000 and start incrementing up to ZZZZZZZ one-time load?
0
 
nico5038Commented:
You can "code" the field hexadecimal and seven digits are then sufficient to store 14 digits.
It won't be possible to print the code without "unpacking" it.
The pseudocode to be used for "packing" is:
CHR(digit 1 * 16 + digit 2) = newdigit 1
CHR(digit 3 * 16 + digit 4) = newdigit 2
etc.
Unpacking is reverse:
ASCII([newdigit 1]) / 16 = digit 1 (in integer format)
ASCII([newdigit 1]) MOD 16 = digit 2

Possible ?

Nic;o)
0
 
tonyraymondAuthor Commented:
It needs to be an algorithm, I have around 80,000 of the 13 digit numbers, I want to be able to feed the 13 digit number, retrieve a 7 alphanumum representation, and use in reverse to get the 13 digit number back.  It isn't a one time load, I will need to add more later.

Thanks,

Tony
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
nico5038Commented:
So my proposal would "fit".....

Nic;o)
0
 
tonyraymondAuthor Commented:
Nico,
Yep, working on it now.

Tony
0
 
nico5038Commented:
Keep in mind it's just "air-code", let me know when you run into trouble ;-)

Nic;o)
0
 
dovholukCommented:
so i tried hex at first too... instead, i came up with a "base 100" approach.

basically i broke it down into this. try this method with as many 13 digit numbers as you have.



Function testmyjobk()

   Dim a As String
   Dim b As String
   
a = "1234567890123"

b = Convert13DigitNumber(a)

MsgBox UnConvert13DigitNumber(b)

End Function


Function Convert13DigitNumber(num As String) As String
Convert13DigitNumber = Left(num, 1) _
   & Chr(Mid(num, 2, 2)) _
   & Chr(Mid(num, 4, 2)) _
   & Chr(Mid(num, 6, 2)) _
   & Chr(Mid(num, 8, 2)) _
   & Chr(Mid(num, 10, 2)) _
   & Chr(Mid(num, 12, 2))
End Function

Function UnConvert13DigitNumber(num As String) As String
UnConvert13DigitNumber = Left(num, 1) _
   & Asc(Mid(num, 2, 1)) _
   & Asc(Mid(num, 3, 1)) _
   & Asc(Mid(num, 4, 1)) _
   & Asc(Mid(num, 5, 1)) _
   & Asc(Mid(num, 6, 1)) _
   & Asc(Mid(num, 7, 1))
End Function


i can comment on "how" it works if you like...

dovholuk

0
 
dovholukCommented:
well... it fails when there are too many 0's in a row... i'm trying to work that out... ;)

be back in a bit...

dovholuk
0
 
dovholukCommented:
got it... the problem was with the string "00" equaling "0"... to correct it, i had to use the RIGHT of 100 + x (returning 01, 00, 05, etc)



Option Compare Database
Option Explicit

Function testmyjobk()

   Dim a As String
   Dim b As String
   
a = "2000000000002"
b = Convert13DigitNumber(a)

MsgBox UnConvert13DigitNumber(b) = a


End Function


Function Convert13DigitNumber(num As String) As String
Convert13DigitNumber = Left(num, 1) _
   & Chr(Mid(num, 2, 2) + 40) _
   & Chr(Mid(num, 4, 2) + 40) _
   & Chr(Mid(num, 6, 2) + 40) _
   & Chr(Mid(num, 8, 2) + 40) _
   & Chr(Mid(num, 10, 2) + 40) _
   & Chr(Mid(num, 12, 2) + 40)
End Function

Function UnConvert13DigitNumber(num As String) As String
UnConvert13DigitNumber = Left(num, 1) _
   & Right(100 + Asc(Mid(num, 2, 1)) - 40, 2) _
   & Right(100 + Asc(Mid(num, 3, 1)) - 40, 2) _
   & Right(100 + Asc(Mid(num, 4, 1)) - 40, 2) _
   & Right(100 + Asc(Mid(num, 5, 1)) - 40, 2) _
   & Right(100 + Asc(Mid(num, 6, 1)) - 40, 2) _
   & Right(100 + Asc(Mid(num, 7, 1)) - 40, 2)
End Function



this should be your ticket...

dovholuk
0
 
dovholukCommented:
by the way, i arbitrarily used 40 as an offset. the original code would use chr(0) and i found that was doing odd things to the resulting strings. also, i used 40 because chr(40) is a "visible" character, vs. spaces, tabs etc. so 40 was a good offset.

enjoy.

dovholuk
0
 
nico5038Commented:
hmm, dovholuk triggered another idea, unpacking the "hex-coded" field can use:
right("00" & hex([newdigit 1]),2)

Nic;o)
0
 
tonyraymondAuthor Commented:
Nico,
This gives me a funny looking output, is this desired?

Public Function SwitchIt()
Dim sample As String
Dim digit1 As Integer
Dim digit2 As Integer
Dim digit3 As Integer
Dim digit4 As Integer
Dim digit5 As Integer
Dim digit6 As Integer
Dim digit7 As Integer
Dim digit8 As Integer
Dim digit9 As Integer
Dim digit10 As Integer
Dim digit11 As Integer
Dim digit12 As Integer
Dim digit13 As Integer
Dim newdigit1 As String
Dim newdigit2 As String
Dim newdigit3 As String
Dim newdigit4 As String
Dim newdigit5 As String
Dim newdigit6 As String
Dim newdigit7 As String
Dim output As String

sample = InputBox("Enter SampleNumber", "Enter Value Please", 1234567890123#)

digit1 = Left(sample, 1)
digit2 = Mid(sample, 2, 1)
digit3 = Mid(sample, 3, 1)
digit4 = Mid(sample, 4, 1)
digit5 = Mid(sample, 5, 1)
digit6 = Mid(sample, 6, 1)
digit7 = Mid(sample, 7, 1)
digit8 = Mid(sample, 8, 1)
digit9 = Mid(sample, 9, 1)
digit10 = Mid(sample, 10, 1)
digit11 = Mid(sample, 11, 1)
digit12 = Mid(sample, 12, 1)
digit13 = Right(sample, 1)

newdigit1 = Chr(digit1 * 16 + digit2)
newdigit2 = Chr(digit3 * 16 + digit4)
newdigit3 = Chr(digit5 * 16 + digit6)
newdigit4 = Chr(digit7 * 16 + digit8)
newdigit5 = Chr(digit9 * 16 + digit10)
newdigit6 = Chr(digit11 * 16 + digit12)
newdigit7 = Chr(digit13 * 16)

output = newdigit1 & newdigit2 & newdigit3 & newdigit4 & newdigit5 & newdigit6 & newdigit7
MsgBox "output is " & output, vbOKOnly


End Function
0
 
nico5038Commented:
That's correct, I "warned" that the code isn't readable.
When no zero's are there you can "read" the coded field back in 13 digits with:
output = Hex(Asc(newdigit1)) & Hex(Asc(newdigit2)) & Hex(Asc(newdigit3)) & Hex(Asc(newdigit4)) & Hex(Asc(newdigit5)) & Hex(Asc(newdigit6)) & Hex(Asc(newdigit7))


Nic;o)
0
 
tonyraymondAuthor Commented:
What I need to do is store that value in a text field.  I might be taking the really long way around the block on this, perhaps it would be better to let an autonumber field be created, and just keep a translation table of Autonumber to actual number??

Dholvul
0
 
nico5038Commented:
An extra table is ofcourse possible, but will ask JOIN's to query.
With a CodeTo and CodeFrom function once written you save that...
BTW why the 7 digit constraint?

Nic;o)
0
 
dovholukCommented:
did you give my solution a try? it converts the 13 digit number into a "compressed" version (much like nico's) but it's a full encode/decode combination...

just run a query such as:

SELECT Your13DigitNumberField, Convert13DigitNumber(Your13DigitNumberField)
FROM YourTable

this will return both the original 13 digit number, AND the converted one. you then run an update on the 7 character field and you're done...

anyway... good luck

dovholuk
0
 
tonyraymondAuthor Commented:
What I need to do is store that value in a text field.  I might be taking the really long way around the block on this, perhaps it would be better to let an autonumber field be created, and just keep a translation table of Autonumber to actual number??

Dholvul
0
 
tonyraymondAuthor Commented:
sorry for the repost of the previous, my browser is whacked today.

Nico,
The 7 digit constraint is due to  production system that was written 40 years ago...  It is funny really, the field can hold 30 characters everywhere except the master that it always refers to...

I don't know if I can use nonletter/numbers or not.  I am going to test now, but I don't think so, so far I can't even use lowercase letters...

Dovholuk,
Thank you also.  This has been a fun one.
0
 
dovholukCommented:
when you say "store the value in a text field", what do you mean and why?

do you mean on a form, you need to store it on a form? or in a table?

does it need to be "displayed" to the users???

any more info?
0
 
nico5038Commented:
If it's on a mainframe, then the "hex-code" I gave you should be changed into:
1234567890123c
By placing in the last digit a hex C (decimal 12), you get a so-called "packed" digit representation you can read with on the mainframe. The "c" value tells it's a neutral (not negative) value. (In COBOL use a Comp-3 field ;-)

My idea was effectively based on that old field format.

Nic;o)
0
 
nico5038Commented:
tonyraymond,

How far are we on this one ?

Nic;o)
0
 
nico5038Commented:

No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in Community Support that this question is:
 - Answered by: nico5038 (50pts) dovholuk (50pts)
Please leave any comments here within the
next seven days.

PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER !

Nic;o)
0
 
NetminderCommented:
Per recommendation, force-accepted.

Netminder
CS Moderator

nico5038: points for you at http://www.experts-exchange.com/jsp/qShow.jsp?ta=msaccess&qid=20332626
0

Featured Post

[Webinar] Kill tickets & tabs using PowerShell

Are you tired of cycling through the same browser tabs everyday to close the same repetitive tickets? In this webinar JumpCloud will show how you can leverage RESTful APIs to build your own PowerShell modules to kill tickets & tabs using the PowerShell command Invoke-RestMethod.

  • 9
  • 6
  • 6
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now