April 25, 2003 at 12:24 pm
Does someone want to tackle this? I have a vb program that calculate a 9th digit where i need 8. Need this in sql server sp
Sub CheckDigit()
Dim Cusip_Array(1 To 8) As Integer
Dim tmp_digit, tmp_resultstring, CusipCheckDigit As String
Dim k, i As Long
Dim sum_digits, tmp_result, PositionChar As Long
Dim tmp_resultleft As Integer
tmp_digit = tmp_CUSIP
k = 1
Do While Len(tmp_digit) >= 1
' make a call to the SOMETHING to find the digital equivalent
Cusip_Array(k) = DigitalEquivalent(Left$(tmp_digit, 1))
k = k + 1
tmp_digit = Mid$(tmp_CUSIP, k)
Loop
' Doubling the second, fourth, sixth, and eighth digital equivalents.
Cusip_Array(2) = Cusip_Array(2) * 2
Cusip_Array(4) = Cusip_Array(4) * 2
Cusip_Array(6) = Cusip_Array(6) * 2
Cusip_Array(8) = Cusip_Array(8) * 2
' Adding all the tens digits together and adding all the ones digits together, then comining the sums.
sum_digits = 0
For i = 1 To 8
' add the ones......
If Cusip_Array(i) >= 1 And Cusip_Array(i) <= 9 Then
sum_digits = sum_digits + Cusip_Array(i)
Else
sum_digits = sum_digits + CInt(Left$(CStr(Cusip_Array(i)), 1))
sum_digits = sum_digits + CInt(Right$(CStr(Cusip_Array(i)), 1))
End If
Next
' Dividing the previous sum by 10. If the result has no factional part or remainder, the check digit is zero. Otherwise, the check
' digit equals the result plus one, times ten, minus the original sum.
' (Check digit = ( result + 1 ) * 10 - Original sum )
tmp_result = (sum_digits / 10)
tmp_resultstring = CStr(tmp_result)
PositionChar = InStr(tmp_resultstring, ".")
If PositionChar = 0 Then
CusipCheckDigit = "0"
Else
tmp_resultleft = CInt(Left$(tmp_resultstring, PositionChar))
CusipCheckDigit = CStr(((tmp_resultleft + 1) * 10) - sum_digits)
End If
tmp_CUSIP = tmp_CUSIP & CusipCheckDigit
exit1:
End Sub
Function DigitalEquivalent(tmp_send As String) As Integer
Select Case tmp_send
Case "0"
tmp_return = 0
Case "1"
tmp_return = 1
Case "2"
tmp_return = 2
Case "3"
tmp_return = 3
Case "4"
tmp_return = 4
Case "5"
tmp_return = 5
Case "6"
tmp_return = 6
Case "7"
tmp_return = 7
Case "8"
tmp_return = 8
Case "9"
tmp_return = 9
Case "A"
tmp_return = 10
Case "B"
tmp_return = 11
Case "C"
tmp_return = 12
Case "D"
tmp_return = 13
Case "E"
tmp_return = 14
Case "F"
tmp_return = 15
Case "G"
tmp_return = 16
Case "H"
tmp_return = 17
Case "I"
tmp_return = 18
Case "J"
tmp_return = 19
Case "K"
tmp_return = 20
Case "L"
tmp_return = 21
Case "M"
tmp_return = 22
Case "N"
tmp_return = 23
Case "O"
tmp_return = 24
Case "P"
tmp_return = 25
Case "Q"
tmp_return = 26
Case "R"
tmp_return = 27
Case "S"
tmp_return = 28
Case "T"
tmp_return = 29
Case "U"
tmp_return = 30
Case "V"
tmp_return = 31
Case "W"
tmp_return = 32
Case "X"
tmp_return = 33
Case "Y"
tmp_return = 34
Case "Z"
tmp_return = 35
Case "*"
tmp_return = 36
Case "@"
tmp_return = 37
Case "#"
tmp_return = 38
End Select
DigitalEquivalent = tmp_return
End Function
April 28, 2003 at 4:05 am
Suggest you make this a permanent table (temp for testing)
create table #equivalent (digit char(1),digitdec int,equiv int,equivdbl int)
insert into #equivalent values ('0',0,0,0)
insert into #equivalent values ('1',1,1,2)
insert into #equivalent values ('2',2,2,4)
insert into #equivalent values ('3',3,3,6)
insert into #equivalent values ('4',4,4,8)
insert into #equivalent values ('5',5,5,1)
insert into #equivalent values ('6',6,6,3)
insert into #equivalent values ('7',7,7,5)
insert into #equivalent values ('8',8,8,7)
insert into #equivalent values ('9',9,9,9)
insert into #equivalent values ('A',10,1,2)
insert into #equivalent values ('B',11,2,4)
insert into #equivalent values ('C',12,3,6)
insert into #equivalent values ('D',13,4,8)
insert into #equivalent values ('E',14,5,10)
insert into #equivalent values ('F',15,6,3)
insert into #equivalent values ('G',16,7,5)
insert into #equivalent values ('H',17,8,7)
insert into #equivalent values ('I',18,9,9)
insert into #equivalent values ('J',19,10,11)
insert into #equivalent values ('K',20,2,4)
insert into #equivalent values ('L',21,3,6)
insert into #equivalent values ('M',22,4,8)
insert into #equivalent values ('N',23,5,10)
insert into #equivalent values ('O',24,6,12)
insert into #equivalent values ('P',25,7,5)
insert into #equivalent values ('Q',26,8,7)
insert into #equivalent values ('R',27,9,9)
insert into #equivalent values ('S',28,10,11)
insert into #equivalent values ('T',29,11,13)
insert into #equivalent values ('U',30,3,6)
insert into #equivalent values ('V',31,4,8)
insert into #equivalent values ('W',32,5,10)
insert into #equivalent values ('X',33,6,12)
insert into #equivalent values ('Y',34,7,14)
insert into #equivalent values ('Z',35,8,7)
insert into #equivalent values ('*',36,9,9)
insert into #equivalent values ('@',37,10,11)
insert into #equivalent values ('#',38,11,13)
create the following table from input params, either substring 8 chars or 8 individual ints
create table #digits (digitid int,digit char(1))
insert into #digits values (1,'4')
insert into #digits values (2,'A')
insert into #digits values (3,'*')
insert into #digits values (4,'4')
insert into #digits values (5,'Q')
insert into #digits values (6,'D')
insert into #digits values (7,'@')
insert into #digits values (8,'2')
declare @sum_digits int, @check_value int, @check_digit int
set @sum_digits = 0
select @sum_digits = @sum_digits + (case when d.digitid in (2,4,6,8) then equivdbl else equiv end)
from #digits d inner join #equivalent e on e.digit = d.digit
set @check_value = @sum_digits / 10
set @check_digit = @sum_digits - (@check_value * 10)
if (@check_digit <> 0)
set @check_digit = ((@check_value + 1) * 10) - @sum_digits
This assumes that 8 chars input match 1 entry in equivalent table.
Far away is close at hand in the images of elsewhere.
Anon.
April 28, 2003 at 4:53 am
I'm sure that this does what you want, but why not convert the function to a DLL? You could use sp_addextendedproc to create an exenteded stored procedure and then call it as you would any other sp?
Jeremy
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply