Can u help me transform this code into sql function?
/* Append modulus 11 check digit to supplied string of digits. */
function GenMOD11( $base_val )
{
$result = "";
$weight = array( 2, 3, 4, 5, 6, 7,
2, 3, 4, 5, 6, 7,
2, 3, 4, 5, 6, 7,
2, 3, 4, 5, 6, 7 );
/* For convenience, reverse the string and work left to right. */
$reversed_base_val = strrev( $base_val );
for ( $i = 0, $sum = 0; $i < strlen( $reversed_base_val ); $i++ )
{
/* Calculate product and accumulate. */
$sum += substr( $reversed_base_val, $i, 1 ) * $weight[ $i ];
}
/* Determine check digit, and concatenate to base value. */
$remainder = $sum % 11;
switch ( $remainder )
{
case 0:
$result = $base_val . 0;
break;
case 1:
$result = "n/a";
break;
default:
$check_digit = 11 - $remainder;
$result = $base_val . $check_digit;
break;
}Owh..i forget.. I'm using SQL Server 2000|||moving thread to SQL Server forum|||Can you explain to us what the above function actually does (step by step preferred) and then we can see if we can help! :)|||The reversing confuses me slightly. Is the last digit always multiplied by 2?|||May I contribute:-- ptp 20070806 SQL Server mod-11 function
-- See http://www.dbforums.com/showthread.php?t=1621130 for discussion
-- Note: Mod-11 was once a well known checkdigit algorithm. It was implemented in hardware
-- on the 129 keypunch, and it still used in ISBN and banking applications in 2007
CREATE FUNCTION dbo.fMod11(@.pcFundus VARCHAR(20))
RETURNS VARCHAR(21) AS
BEGIN
DECLARE @.iAccumulator BIGINT -- Accumulator for weighted sum
, @.iDigits INT -- Digit place value
, @.iNoise INT -- Noise characters ignored
, @.cChar CHAR(1) -- Current working character
, @.cResult VARCHAR(21) -- Result value to return
, @.cWork VARCHAR(21) -- Scratch buffer
SET @.cResult = @.pcFundus -- Assume we return what we got
SET @.cWork = Reverse(@.pcFundus) -- Reverse to make string handling simpler
SET @.iAccumulator = 0 -- Accumulator starts at zero
SET @.iDigits = 1 -- 1 is offest for the check digit
WHILE 0 < Len(@.cWork) -- Loop to process all characters
BEGIN
SET @.cChar = Left(@.cWork, 1) -- Current char is leftmost
SET @.cWork = SubString(@.cWork, 2, 21) -- then peel it off the buffer
IF 0 < CharIndex(@.cChar, '0123456789') -- Digit character
BEGIN
SET @.iDigits = 1 + @.iDigits -- bump digit count
SET @.iAccumulator = @.iDigits * (Ascii(@.cChar) - 48) + @.iAccumulator
END
ELSE IF 0 < CharIndex(@.cChar, ' -') -- Defined "noise" character?
SET @.iNoise = 1 + @.iNoise
ELSE -- Garbage, bail out!
BEGIN
SET @.cResult = NULL
SET @.cWork = ''
END
END
RETURN @.cResult + SubString('0123456789XX', 12 - @.iAccumulator % 11, 1)
END
GO
SELECT d1 + d0, dbo.fMod11(d1 + d0) -- Prove that we've got it correct
FROM (
SELECT 0 AS d0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) AS z0
CROSS JOIN (
SELECT 0 AS d1 UNION SELECT 10 UNION SELECT 20 UNION SELECT 30 UNION SELECT 40
UNION SELECT 50 UNION SELECT 60 UNION SELECT 70 UNION SELECT 80 UNION SELECT 90) AS z1
ORDER BY d1 + d0
GO
DROP FUNCTION dbo.fMod11 -- Tidy up after we've played in the sandbox-PatP|||Pat, I don't believe that your @.iDigits does the same thing as massspectrometry's weight array as the string gets longer than 6 characters.
You seem to multiply the 7th number by 8, and mass's function multiplies it by 2.|||I'll conceed that my Transact-SQL function and the PHP function aren't identical.
My algorithm implements Mod-11 as it is used for ISBN, banking, etc. It specifically allows for "noise characters" that are permissible in those uses, and it correctly computes the checksum for a fundus value with a value of zero or a remainder of ten (using an X for the checksum character).
-PatP|||Can you explain to us what the above function actually does (step by step preferred) and then we can see if we can help! :)
Hai..thank you for offering.. I'm trying to make a sql function that will perform similarly like this url http://www.eclectica.ca/howto/modulus-11-self-check.php
I have 7 digits data (SERIALNO), and i want to get the modulus 11 from this 7 digits data..
For example..
SERIALNO || MODULO 11
1000001 || 10000017
1000002 || 10000025
1000003 || 10000033
1000004 || 10000041
Can it be done? How?|||Here is the code with the 5250 bug faithfully re-implemented:-- ptp 20070806 SQL Server implemented of IBM 5250 mod-11 function
-- See http://www.dbforums.com/showthread.php?t=1621130 for discussion
-- Note: Mod-11 was once a well known checkdigit algorithm. A derivative of mod-11
-- was implemented in hardware on the 5250 terminal
CREATE FUNCTION dbo.fMod11(@.pcFundus VARCHAR(20))
RETURNS VARCHAR(21) AS
BEGIN
DECLARE @.iAccumulator BIGINT -- Accumulator for weighted sum
, @.iDigits INT -- Digit place value
, @.iNoise INT -- Noise characters ignored
, @.cChar CHAR(1) -- Current working character
, @.cResult VARCHAR(21) -- Result value to return
, @.cWork VARCHAR(21) -- Scratch buffer
SET @.cResult = @.pcFundus -- Assume we return what we got
SET @.cWork = Reverse(@.pcFundus) -- Reverse to make string handling simpler
SET @.iAccumulator = 0 -- Accumulator starts at zero
SET @.iDigits = 1 -- 1 is offest for the check digit
WHILE 0 < Len(@.cWork) -- Loop to process all characters
BEGIN
SET @.cChar = Left(@.cWork, 1) -- Current char is leftmost
SET @.cWork = SubString(@.cWork, 2, 21) -- then peel it off the buffer
IF 0 < CharIndex(@.cChar, '0123456789') -- Digit character
BEGIN
SET @.iDigits = -- Next 5250 digit weight
CASE
WHEN 7 = @.iDigits THEN 2
ELSE 1 + @.iDigits
END
SET @.iAccumulator = @.iDigits * (Ascii(@.cChar) - 48) + @.iAccumulator
END
ELSE IF 0 < CharIndex(@.cChar, ' -') -- Defined "noise" character?
SET @.iNoise = 1 + @.iNoise
ELSE -- Garbage, bail out!
BEGIN
SET @.cResult = NULL
SET @.cWork = ''
END
END
RETURN @.cResult + SubString('0123456789XX', 12 - @.iAccumulator % 11, 1)
END
GO
SELECT dbo.fMod11('100000' + d)
FROM (SELECT '0' AS d UNION SELECT '1' UNION SELECT '2' UNION SELECT '3' UNION SELECT '4' UNION SELECT '5') AS z
SELECT d1 + d0, dbo.fMod11(d1 + d0) -- Prove that we've got it correct
FROM (
SELECT 0 AS d0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4
UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9) AS z0
CROSS JOIN (
SELECT 0 AS d1 UNION SELECT 10 UNION SELECT 20 UNION SELECT 30 UNION SELECT 40
UNION SELECT 50 UNION SELECT 60 UNION SELECT 70 UNION SELECT 80 UNION SELECT 90) AS z1
ORDER BY d1 + d0
GO
DROP FUNCTION dbo.fMod11 -- Tidy up after we've played in the sandbox-PatP|||Erm.. i feel really shy of asking this.. how to call the function? (embarrassed)|||See the sample code at the end of the snippet that I posted. Your test is in there.
-PatP|||Thank you pat phelan.. in the code here :
SELECT dbo.fMod11('100000' + d)
FROM (SELECT '0' AS d UNION SELECT '1' UNION SELECT '2' UNION SELECT '3' UNION SELECT '4' UNION SELECT '5') AS z
For example if i have 100,000 data.. starting from 1000001 until 1100000.. How am i to code it? Is it one by one?|||FYI - http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=87357|||None of the code or links worked for my situation. I'm posting what I hope is a generic mod 11 user defined function. I'm sure there are improvements that could be made to it, but it works for my need (10 numeric digits, last digit being a mod11 check digit).
/*
Checks if a number passes the Mod11 checksum algorithm.
Input:
@.NumberToCheck = the number to check for validity (last digit is the check digit)
Returns:
1 = the number provided passes the Mod11 checksum algorithm
0 = the number provided does not pass the Mod11 checksum algorithm
*/
CREATE Function dbo.ufn_Mod11(@.NumberToCheck as varchar(10))
Returns bit As
Begin
/*
-- FOR TESTING --------
Declare @.NumberToCheck as varchar(10)
Set @.NumberToCheck = '7830834260'
Set @.NumberToCheck = '7806812519'
-- FOR TESTING --------
*/
Declare @.CheckDigit int
Declare @.Counter int
Declare @.IsValid bit
Declare @.Product int
Declare @.Sum int
-- Assume it is not valid.
Set @.IsValid = 0
-- Number must not be null, must be numeric, must be ten digits.
If @.NumberToCheck Is Not Null And IsNumeric(@.NumberToCheck) = 1 And Len(@.NumberToCheck) = 10
Begin
Set @.Counter = 1
Set @.Sum = 0
-- Reverse the number being checked.
Set @.NumberToCheck = Reverse(@.NumberToCheck)
-- Iterate through all digits except the last digit.
While @.Counter <= Len(@.NumberToCheck)
Begin
-- Multiply the digit by its position, starting with the second one.
If @.Counter > 1
Begin
Set @.Product = SubString(@.NumberToCheck, @.Counter, 1)
Set @.Product = @.Product * @.Counter
-- Sum the current product.
Set @.Sum = @.Sum + @.Product
End
Set @.Counter = @.Counter + 1
End
Set @.CheckDigit = @.Sum % 11
-- If the check digit is ten, just set it to zero.
If @.CheckDigit = 10
Begin
Set @.CheckDigit = 0
End
-- Compare the calculated check digit to the original last digit,
-- which is now the first since it was reversed.
If @.CheckDigit = Left(@.NumberToCheck, 1)
Begin
Set @.IsValid = 1
End
End
Return @.IsValid
End
R.K.S. wrote: