Modulus 11 - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Modulus 11

Author

Topic

massspectrometry
Yak Posting Veteran

50 Posts

Posted - 2007-08-04 : 01:21:31

Hai there.. I need some help.. badly.. I have a data that looks like this..

SERIAL MOD 11 (2 columns)
1000001 W
1000002 X
1000003 Y
1000004 Z

The (W,X,Y,Z) is the result of the modulus 11 of the serial number.

Important :
U can click here/copy and paste the link for the example of mod 11
[url]http://www.eclectica.ca/howto/modulus-11-self-check.php?[/url]

According to the website :

SERIAL MOD 11 (2 columns)
01000 6
01002 2
01005 7
01006 5

Can it be implemented in SQL? If so, how?

Please help me.. :(

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-08-04 : 03:56:35

Yes it can be done. Here is an example of how to implement the LUHN algorithm.
It can easily be changed to MODULES 11 algorithm.

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=76195

E 12°55'05.25"
N 56°04'39.16"

massspectrometry
Yak Posting Veteran

50 Posts

Posted - 2007-08-05 : 21:51:07

Erm.. i really don't know how :( :(( Please give me a lead... :(

massspectrometry
Yak Posting Veteran

50 Posts

Posted - 2007-08-05 : 22:31:04

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

massspectrometry
Yak Posting Veteran

50 Posts

Posted - 2007-08-06 : 00:51:15

SQL Team.. pls help me.. :((

Clages1
Yak Posting Veteran

69 Posts

Posted - 2007-08-06 : 20:36:13

Hi
here a SP i have using
change for your need
att
Carlos Lages

CREATE FUNCTION Modulo11(@VALOR varchar(60))
RETURNS CHAR(1)
AS
BEGIN
DECLARE
@TOTAL INT,
@COUNT INT,
@weight INT,
@DIGITO INT,
@RETORNO CHAR(1),
@BASE INT,
@RESTO BIT

SET @TOTAL = 0
SET @weight = 2
SET @BASE = 9
SET @RESTO = 0
SET @COUNT = Len(@VALOR)

LOOP:
BEGIN
SET @TOTAL = @TOTAL + (Convert(int, SubString(@VALOR, @COUNT, 1)) * @weight)
IF (@weight < @BASE)
SET @weight = @weight + 1
ELSE
SET @weight = 2
SET @COUNT = @COUNT-1
END
IF @COUNT >= 1 GOTO LOOP

IF (@RESTO = 1)
BEGIN
SET @RETORNO = (@TOTAL % 11)
END
ELSE
BEGIN
SET @DIGITO = 11 - (@TOTAL % 11)
IF (@DIGITO > 9) SET @DIGITO = 0
SET @RETORNO = @DIGITO
END
RETURN @RETORNO
END
GO

/* sample :
*/
SELECT dbo.Modulo11('98710223423')

massspectrometry
Yak Posting Veteran

50 Posts

Posted - 2007-08-06 : 21:08:26

Hai there.. can u tell me what the function above is doing?

Clages1
Yak Posting Veteran

69 Posts

Posted - 2007-08-06 : 21:37:22

Save the function and try it

like the sample

SELECT dbo.Modulo11('98710223423')

you will get a digit from a modulus 11 for this number
the digits is 2

tks
Carlos Lages

massspectrometry
Yak Posting Veteran

50 Posts

Posted - 2007-08-06 : 22:09:37

Yeah i did try.. But when i test for this number, the result is

SELECT dbo.Modulo11('1000001') = 1
SELECT dbo.Modulo11('1000002') = 0

i thought it was suppose to return this number

SELECT dbo.Modulo11('1000001') = 7
SELECT dbo.Modulo11('1000002') = 5

???

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-08-07 : 00:31:52

directly translate over to T-SQL

/* Append modulus 11 check digit to supplied string of digits. */
-- FUNCTION GenMOD11( $base_val )
-- {
CREATE FUNCTION GenMOD11( @base_val varchar(100))
RETURNS varchar(100)
AS
BEGIN
	DECLARE	@result			varchar(100),
		@reversed_base_val	varchar(100),
		@weight			varchar(10),
		@i			int,
		@SUM			int,
		@remainder		int

--    	$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 );
	SELECT	@result = '',
		@weight	= '234567234567234567234567'

   	/* for convenience, reverse the string and work left to right. */
--    	$reversed_base_val = strrev( $base_val );
	SELECT	@reversed_base_val = REVERSE(@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 ];
-- 	}
	SELECT	@i 	= 1,
		@sum	= 0

	WHILE (@i <= LEN(@reversed_base_val))
	BEGIN
		/* Calculate product and accumulate. */
		SELECT	@sum = 	@sum + (CONVERT(int, SUBSTRING(@reversed_base_val, @i, 1)) * 
					CONVERT(int, SUBSTRING(@weight, @i, 1)) )
		SELECT	@i = @i + 1
	END

	/* Determine check digit, and concatenate to base value. */
--    	$remainder = $sum % 11;
	SELECT	@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;
--    	}

	SELECT	@result	= @base_val +
			 	CASE 	@remainder
				WHEN	0		THEN '0'
				WHEN	1		THEN 'n/a'
				ELSE	CONVERT(varchar(2), 11 - @remainder)
				END
	RETURN 	@result
END
go

SELECT	serial, dbo.GenMOD11(serial) 
FROM	(
		SELECT	serial = '167703625'	UNION ALL
		SELECT	serial = '1000001'	UNION ALL
		SELECT	serial = '1000002'
	) s

/*
serial                                                                                                         
--------- ----------
167703625 1677036253
1000001   10000017
1000002   10000025
*/

KH
[spoiler]Time is always against us[/spoiler]

pootle_flump

1064 Posts

Posted - 2007-08-07 : 03:09:07

FYI - http://www.dbforums.com/showthread.php?t=1621130

massspectrometry
Yak Posting Veteran

50 Posts

Posted - 2007-08-07 : 03:36:42

Thanks khtan! Really2 thanks

massspectrometry
Yak Posting Veteran

50 Posts

Posted - 2007-08-07 : 04:01:02

quote:
Originally posted by pootle_flump

FYI - http://www.dbforums.com/showthread.php?t=1621130

i know where my thread are..thanks

pootle_flump

1064 Posts

Posted - 2007-08-07 : 06:31:16

quote:
Originally posted by massspectrometry

quote:
Originally posted by pootle_flump

FYI - http://www.dbforums.com/showthread.php?t=1621130

i know where my thread are..thanks

Wasn't for your benefit

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources