Results 1 to 15 of 15
  1. #1
    Join Date
    Jul 2007
    Posts
    13

    Unanswered: Convert to SQL Function! Help!

    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;
    }

  2. #2
    Join Date
    Jul 2007
    Posts
    13
    Owh..i forget.. I'm using SQL Server 2000

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    moving thread to SQL Server forum
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Can you explain to us what the above function actually does (step by step preferred) and then we can see if we can help!
    George
    Home | Blog

  5. #5
    Join Date
    Nov 2002
    Posts
    272
    The reversing confuses me slightly. Is the last digit always multiplied by 2?

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    May I contribute:
    Code:
    --  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

  7. #7
    Join Date
    Nov 2002
    Posts
    272
    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.

  8. #8
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    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

  9. #9
    Join Date
    Jul 2007
    Posts
    13
    Quote Originally Posted by georgev
    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?

  10. #10
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Here is the code with the 5250 bug faithfully re-implemented:
    Code:
    --  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

  11. #11
    Join Date
    Jul 2007
    Posts
    13
    Erm.. i feel really shy of asking this.. how to call the function? (embarrassed)

  12. #12
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    See the sample code at the end of the snippet that I posted. Your test is in there.

    -PatP

  13. #13
    Join Date
    Jul 2007
    Posts
    13
    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?

  14. #14
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Testimonial:
    pootle flump
    ur codings are working excelent.

  15. #15
    Join Date
    Oct 2007
    Posts
    1
    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).

    Code:
    /*
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •