SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 Script Library
 Luhn algorithm
 New Topic  Reply to Topic
 Printer Friendly
Next Page
Author Previous Topic Topic Next Topic
Page: of 2

SwePeso
Patron Saint of Lost Yaks

Sweden
29910 Posts

Posted - 12/11/2006 :  08:19:11  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Use this to check if Luhn has valid check digit
CREATE FUNCTION	dbo.fnIsLuhnValid
(
	@Luhn VARCHAR(8000)
)
RETURNS BIT
AS

BEGIN
	IF @Luhn LIKE '%[^0-9]%'
		RETURN 0

	DECLARE	@Index SMALLINT,
		@Multiplier TINYINT,
		@Sum INT,
		@Plus TINYINT

	SELECT	@Index = LEN(@Luhn),
		@Multiplier = 1,
		@Sum = 0

	WHILE @Index >= 1
		SELECT	@Plus = @Multiplier * CAST(SUBSTRING(@Luhn, @Index, 1) AS TINYINT),
			@Multiplier = 3 - @Multiplier,
			@Sum = @Sum + @Plus / 10 + @Plus % 10,
			@Index = @Index - 1

	RETURN CASE WHEN @Sum % 10 = 0 THEN 1 ELSE 0 END
END

Peter Larsson
Helsingborg, Sweden

Edited by - SwePeso on 01/11/2007 07:15:27

SwePeso
Patron Saint of Lost Yaks

Sweden
29910 Posts

Posted - 12/11/2006 :  08:34:06  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Use this to add the Luhn character check digit
CREATE FUNCTION	dbo.fnGetLuhn
(
	@Luhn VARCHAR(7999)
)
RETURNS VARCHAR(8000)
AS

BEGIN
	IF @Luhn LIKE '%[^0-9]%'
		RETURN @Luhn

	DECLARE	@Index SMALLINT,
		@Multiplier TINYINT,
		@Sum INT,
		@Plus TINYINT

	SELECT	@Index = LEN(@Luhn),
		@Multiplier = 2,
		@Sum = 0

	WHILE @Index >= 1
		SELECT	@Plus = @Multiplier * CAST(SUBSTRING(@Luhn, @Index, 1) AS TINYINT),
			@Multiplier = 3 - @Multiplier,
			@Sum = @Sum + @Plus / 10 + @Plus % 10,
			@Index = @Index - 1

	RETURN	@Luhn + CASE WHEN @Sum % 10 = 0 THEN '0' ELSE CAST(10 - @Sum % 10 AS CHAR) END
END

Peter Larsson
Helsingborg, Sweden

Edited by - SwePeso on 01/11/2007 07:17:16
Go to Top of Page

rockmoose
SQL Natt Alfen

Sweden
3279 Posts

Posted - 12/11/2006 :  15:08:02  Show Profile  Reply with Quote
Neat.
Any caveats using the luhn algorithm?
Are there some types of credit card numbers that do not adhere to this for instance?

rockmoose
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29910 Posts

Posted - 12/11/2006 :  15:26:13  Show Profile  Visit SwePeso's Homepage  Reply with Quote
No one that I am aware of.
All credit cards with VISA, American Express and Mastercard are compatible with Luhn algorithm.
And as you know Rocky, Luhn algorithm is also applied to Swedish birthnumbers (SSN) and company organization numbers.
OCR numbers for internet payments also use Luhn algorithm.

Yes, there are some caveats. A few are serious.
Luhn algorithm does NOT check for substitution. If you have number 20061211, you can interchange all even placed characters (and all odd placed characters for that matter) and still get same Luhn code.

20061211 and 20021611 will produce same Luhn check digit.

One the advantages of the algorithm is that it is fairly fast and the uniqueness of the checkdigit is high enough to filter out most obvious wrong-typings.


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

rockmoose
SQL Natt Alfen

Sweden
3279 Posts

Posted - 12/11/2006 :  15:34:12  Show Profile  Reply with Quote
Ok, thanks Peter!
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29910 Posts

Posted - 12/11/2006 :  15:54:39  Show Profile  Visit SwePeso's Homepage  Reply with Quote
See more here
http://en.wikipedia.org/wiki/Luhn_algorithm


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 12/11/2006 :  17:20:35  Show Profile  Reply with Quote
quote:
Originally posted by Peso

See more here
http://en.wikipedia.org/wiki/Luhn_algorithm


Peter Larsson
Helsingborg, Sweden



Are you going to edit the Wikipedia article to add an External Link to this script?



CODO ERGO SUM
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29910 Posts

Posted - 12/11/2006 :  17:22:42  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Can I do that?


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29910 Posts

Posted - 12/11/2006 :  17:25:15  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Cool!
Thanks Michael.


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

Jeff Moden
Aged Yak Warrior

USA
649 Posts

Posted - 12/28/2006 :  17:29:45  Show Profile  Reply with Quote
@Multiple... THAT was the key... That is MUCH faster than doing the reverse I was doing... I cut 50% off the time of the following by eliminating the REVERSE I was using by using @Multiple instead... Brilliant code, Peter. Thought I'd share my final result with you... includes a conditional "remove non-numeric characters" script...

drop function fnCheckLuhn10
GO
CREATE FUNCTION dbo.fnCheckLuhn10
/**********************************************************************************************************************
 The function accepts a credit card or other number either as a VARCHAR or an INT and returns a 1 if the numbers match
 the LUHN 10 checksum specification and 0 if not.

 The input number does NOT need to be digits only.  Numbers like 1234-5678-9012-3456 or 1234 5678 9012 3456 are
 acceptable.

 Revision history:
 Rev 00 - 03/08/2005 - Jeff Moden - Initial creation and test.
 Rev 01 - 12/28/2006 - Jeff Moden - Performance enhancement using @Multiple thanks to Peter Larson
**********************************************************************************************************************/
--===== Declare I/O parameters
        (
        @Luhn VARCHAR(8000)
        )
RETURNS INT
     AS
  BEGIN
--=====================================================================================================================
--===== Declare local variables
DECLARE @CleanedLuhn  VARCHAR(8000),          --The Luhn number stripped of non-numeric characters
        @DigitProduct INT,                    --The result of multiplying the digit times the multiplier
        @Multiplier   INT,                    --1 for odd position digits, 2 for even position digits
        @Sum          INT,                    --The Luhn 10 sum
        @WorkLuhn     VARCHAR(8000)           --The clean Luhn number 

--===== If present, remove all non-digit characters
     IF PATINDEX('%[^0-9]%',@Luhn) > 0        --If any non-digit characters exist, then...
 SELECT @CleanedLuhn = ISNULL(@CleanedLuhn,'') 
                     + SUBSTRING(@Luhn,t.N,1)
   FROM dbo.Tally t                           --Contains a list of whole numbers
  WHERE t.N <= LEN(@Luhn)                     --Limits the join/set based "loop" to the length of the Luhn
    AND SUBSTRING(@Luhn,t.N,1) LIKE '[0-9]'   --Non-digits are ignored, only 0-9 are included

--===== Presets
     -- Note: Use the cleaned Luhn if it needed cleaning or the original Luhn if not
 SELECT @Sum         = 0,
        @Multiplier  = 1,
        @WorkLuhn    = ISNULL(@CleanedLuhn,@Luhn)

--===== Calculate the Luhn 10 sum
 SELECT @DigitProduct = @Multiplier           --1 for odd numbers, 2 for even numbers
                      * SUBSTRING(@WorkLuhn, t.N, 1), --A given digit in the Luhn
        @Sum          = @Sum                  --Luhn 10 sum starts at 0
                      + @DigitProduct / 10    --The 1st digit for products > 9, 0 for product < 10
                      + @DigitProduct % 10,   --The 2nd digit for products > 9 or only digit for product < 10
        @Multiplier   = 3 - @Multiplier       --3-1=2, then 3-2=1, repeats  
   FROM dbo.Tally t WITH (NOLOCK)             --Contains a list of whole numbers
  WHERE t.N <= LEN(@WorkLuhn)                 --Limits the join/set based "loop" to the length of the cleaned Luhn
  ORDER BY t.N DESC

--===== If the sum is evenly divisible by 10, then check is ok... return 1.  
     -- Otherwise, return 0 as "Failed" check
 RETURN 1-SIGN(@SUM%10)
--=====================================================================================================================
    END

And, for those that haven't created a Tally table, yet... now's the time...
--===== Create and populate the Tally table on the fly
 SELECT TOP 11000 --equates to more than 30 years of dates
        IDENTITY(INT,1,1) AS N
   INTO dbo.Tally
   FROM Master.dbo.SysColumns sc1,
        Master.dbo.SysColumns sc2

--===== Add a Primary Key to maximize performance
  ALTER TABLE dbo.Tally
        ADD CONSTRAINT PK_Tally_N PRIMARY KEY CLUSTERED (N)

--===== Allow the general public to use it
  GRANT SELECT ON dbo.Tally TO PUBLIC

--Jeff Moden

Edited by - Jeff Moden on 12/28/2006 17:54:08
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29910 Posts

Posted - 12/31/2006 :  07:51:43  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Great work Jeff!

However... The Multiplier value is not guaranteed to be 1 for all odd positions and 2 for all even positions.
The algorithm states that the multiplier value is 1 for all odd positions COUNTED RIGTH TO LEFT, and 2 for all even positions COUNTED RIGHT TO LEFT.

Also I am interested how much faster you algorithm is for a table of 8 million records with 10 digit varchar values.


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

Jeff Moden
Aged Yak Warrior

USA
649 Posts

Posted - 12/31/2006 :  08:53:55  Show Profile  Reply with Quote
Take a look at the code Peter... ORDER BY DESC... Same as @Index-1 in yours.

And didn't mean to imply that mine was faster than yours... what I meant was your technique of using @Multiple made my code faster than what it previously was because I didn't need to use REVERSE anymore.

However, you've spiked my curiosity and I'll check.

--Jeff Moden

Edited by - Jeff Moden on 12/31/2006 08:57:50
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29910 Posts

Posted - 12/31/2006 :  12:02:53  Show Profile  Visit SwePeso's Homepage  Reply with Quote
There was no irony nor pun in my reply. I was just reading the line comments you wrote. Later I saw the ORDER BY clause.

I am indeed interested in speed!


Peter Larsson
Helsingborg, Sweden

Edited by - SwePeso on 12/31/2006 12:09:13
Go to Top of Page

Jeff Moden
Aged Yak Warrior

USA
649 Posts

Posted - 12/31/2006 :  14:37:06  Show Profile  Reply with Quote
Yeah, if you didn't look at the code, the comments are misleading... I've gotta fix the comments in the code, for sure! Thanks for the tip.

I'm running the code for the 8M rows on a two cpu test box at work (remotely, of course )...

I did some preliminary tests on my single CPU box at home... the While loop solution usually beats the Tally table solution for tables sizes less than 1300 rows by and average of 8-10 % with swings from .5% to 20%. Both peg the CPU (they're both very fast) during the duration of their run so probably not an accurate test. That's why I switched to the quiet test box at work...

I'll post the test code and the results as soon as it's done. The test code does 10 runs for each function and stores the accumulated time. I estimate it takes 5 minutes +/- 15 seconds for each run and with 20 total runs, it'll take about an hour +40 or so ...

--Jeff Moden

Edited by - Jeff Moden on 12/31/2006 14:42:50
Go to Top of Page

Jeff Moden
Aged Yak Warrior

USA
649 Posts

Posted - 12/31/2006 :  16:03:27  Show Profile  Reply with Quote
Here's the results... basically, it a wash at the 8 million row level. For some reason, the "averages" don't appear to be displaying correctly and I dunno why... they are made from the total of all runs for each function divided by the number of runs. The totals are accumulated in milliseconds...

============================================================================================
Testing fnIsLuhnValid (uses WHILE loop)...
--------------------------------------------------------------------------------------------
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
  Run # 1: 00:05:22:463
--------------------------------------------------------------------------------------------
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
  Run # 2: 00:05:04:053
--------------------------------------------------------------------------------------------
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
  Run # 3: 00:05:04:620
--------------------------------------------------------------------------------------------
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
  Run # 4: 00:05:01:247
--------------------------------------------------------------------------------------------
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
  Run # 5: 00:05:04:510
--------------------------------------------------------------------------------------------
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
  Run # 6: 00:05:00:290
--------------------------------------------------------------------------------------------
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
  Run # 7: 00:05:01:447
--------------------------------------------------------------------------------------------
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
  Run # 8: 00:04:56:633
--------------------------------------------------------------------------------------------
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
  Run # 9: 00:05:00:710
--------------------------------------------------------------------------------------------
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
  Run # 10: 00:04:56:760
--------------------------------------------------------------------------------------------
============================================================================================
Testing fnCheckLuhn10 (uses Tally table)...
--------------------------------------------------------------------------------------------
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
  Run # 1: 00:05:02:260
--------------------------------------------------------------------------------------------
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
  Run # 2: 00:04:43:683
--------------------------------------------------------------------------------------------
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
  Run # 3: 00:04:49:853
--------------------------------------------------------------------------------------------
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
  Run # 4: 00:04:41:637
--------------------------------------------------------------------------------------------
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
  Run # 5: 00:04:41:960
--------------------------------------------------------------------------------------------
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
  Run # 6: 00:04:45:747
--------------------------------------------------------------------------------------------
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
  Run # 7: 00:04:40:833
--------------------------------------------------------------------------------------------
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
  Run # 8: 00:04:47:633
--------------------------------------------------------------------------------------------
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
  Run # 9: 00:04:41:117
--------------------------------------------------------------------------------------------
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
  Run # 10: 00:04:41:230
--------------------------------------------------------------------------------------------
============================================================================================
   ** Final Stats **** Final Stats **** Final Stats **** Final Stats **** Final Stats **
 
   Average Run Time fnCheckLuhn10: 00:04:45:597 (with minor conversion error (+/- 3.3 ms))
   Average Run Time fnIsLuhnValid: 00:05:03:273 (with minor conversion error (+/- 3.3 ms))
 
   Total Run Time fnCheckLuhn10: 00:47:35:950
   Total Run Time fnIsLuhnValid: 00:50:32:730
 
   fnCheckLuhn10 is faster by 5.8291% Overall
 
   ** Final Stats **** Final Stats **** Final Stats **** Final Stats **** Final Stats **


Here's the code I tested with (assumes you have both functions and the Tally table I previously posted which may be a bit different than most)...

--http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=76195
go
--=======================================================================================
--      Create test table with 8 Mega-rows random 10 digit numbers as VARCHAR(10) implied
--=======================================================================================
  PRINT REPLICATE('=',92)
  PRINT 'Building the test table...'
 SELECT TOP 8000000
        RowNum  = IDENTITY(INT,1,1),
        Digits  = STR(RAND(CAST(NEWID() AS VARBINARY))*9,1)
                + STR(RAND(CAST(NEWID() AS VARBINARY))*9,1)
                + STR(RAND(CAST(NEWID() AS VARBINARY))*9,1)
                + STR(RAND(CAST(NEWID() AS VARBINARY))*9,1)
                + STR(RAND(CAST(NEWID() AS VARBINARY))*9,1)
                + STR(RAND(CAST(NEWID() AS VARBINARY))*9,1)
                + STR(RAND(CAST(NEWID() AS VARBINARY))*9,1)
                + STR(RAND(CAST(NEWID() AS VARBINARY))*9,1)
                + STR(RAND(CAST(NEWID() AS VARBINARY))*9,1)
                + STR(RAND(CAST(NEWID() AS VARBINARY))*9,1)
   INTO dbo.BigTest
   FROM Master.dbo.SysColumns sc1,
        Master.dbo.SysColumns sc2

--===== Add a primary key like any good table, index is just for "fun"
     -- because an INDEX SEEK is not possible.
  ALTER TABLE dbo.BigTest
    ADD CONSTRAINT PK_BigTest_RowNum PRIMARY KEY CLUSTERED (RowNum)

 CREATE INDEX IX_BigTest_Digits ON dbo.BigTest (Digits)

  PRINT REPLICATE('=',92)
GO
--=======================================================================================
--      Declare local variables
--      (Note: FLOAT used for datetime calcs)
--=======================================================================================
DECLARE @BB         INT      --Bit bucket to keep display times out of picture
DECLARE @StartTime  DATETIME --To measure simple duration of each run
DECLARE @StopTime   DATETIME --To measure simple duration of each run
DECLARE @WhileDur   FLOAT    --Accumulated duration of the fnIsLuhnValid runs (ms)
DECLARE @TallyDur   FLOAT    --Accumulated duration of the fnCheckLuhn10 runs (ms)
DECLARE @Counter    INT      --General purpose loop counter
DECLARE @RunCount   FLOAT    --Number of times to run each function
    SET @RunCount   = 10

--=======================================================================================
--      Test the fnIsLuhnValid function (uses a WHILE loop in it)
--=======================================================================================
--===== Setup
  PRINT REPLICATE('=',92)
  PRINT 'Testing fnIsLuhnValid (uses WHILE loop)...'
  PRINT REPLICATE('-',92)
    SET @WhileDur = 0
    SET @Counter  = 1
  WHILE @Counter <= @RunCount
  BEGIN
        --===== Clear cache and start the time
           DBCC FREEPROCCACHE
           DBCC DROPCLEANBUFFERS
            SET @StartTime = GETDATE()

        --===== Test the function without displaying results to keep display
             -- times from interfering with the test
         SELECT @BB = dbo.fnIsLuhnValid(Digits)
           FROM dbo.BigTest WITH (NOLOCK)

        --===== Stop the timer, display the run duration and accumulate it
            SET @StopTime = GETDATE()
          PRINT '  Run # ' + CAST(@Counter AS VARCHAR(10)) + ': ' 
              + CONVERT(CHAR(12),@StopTime-@StartTime,114)
          PRINT REPLICATE('-',92)
            SET @WhileDur = @WhileDur + DATEDIFF(ms,@StartTime,@StopTime)

        --===== Bump the loop counter
            SET @Counter = @Counter + 1          
    END

--=======================================================================================
--      Test the fnCheckLuhn10 function (uses a TALLY table instead of a loop in it)
--=======================================================================================
--===== Setup
  PRINT REPLICATE('=',92)
  PRINT 'Testing fnCheckLuhn10 (uses Tally table)...'
  PRINT REPLICATE('-',92)
    SET @TallyDur = 0
    SET @Counter  = 1
  WHILE @Counter <= @RunCount
  BEGIN
        --===== Clear cache and start the time
           DBCC FREEPROCCACHE
           DBCC DROPCLEANBUFFERS
            SET @StartTime = GETDATE()

        --===== Test the function without displaying results to keep display
             -- times from interfering with the test
         SELECT @BB = dbo.fnCheckLuhn10(Digits)
           FROM dbo.BigTest WITH (NOLOCK)

        --===== Stop the timer, display the run duration and accumulate it
            SET @StopTime = GETDATE()
          PRINT '  Run # ' + CAST(@Counter AS VARCHAR(10)) + ': ' 
              + CONVERT(CHAR(12),@StopTime-@StartTime,114)
          PRINT REPLICATE('-',92)
            SET @TallyDur = @TallyDur + DATEDIFF(ms,@StartTime,@StopTime)

        --===== Bump the loop counter
            SET @Counter = @Counter + 1          
    END

--=======================================================================================
--      Print the run stats
--=======================================================================================
  PRINT REPLICATE('=',92)
  PRINT SPACE(3) + REPLICATE('** Final Stats **',5)

--===== Average duration stats
  PRINT ' '
  PRINT SPACE(3) + 'Average Run Time fnCheckLuhn10: ' 
      + CONVERT(CHAR(12),DATEADD(ms,@TallyDur/@RunCount,0),114)
      + ' (with minor conversion error (+/- 3.3 ms))'
  PRINT SPACE(3) + 'Average Run Time fnIsLuhnValid: ' 
      + CONVERT(CHAR(12),DATEADD(ms,@WhileDur/@RunCount,0),114)
      + ' (with minor conversion error (+/- 3.3 ms))'

--===== Total duration stats
  PRINT ' '
  PRINT SPACE(3) + 'Total Run Time fnCheckLuhn10: ' 
      +  CONVERT(CHAR(12),DATEADD(ms,@TallyDur,0),114)
  PRINT SPACE(3) + 'Total Run Time fnIsLuhnValid: ' 
      +  CONVERT(CHAR(12),DATEADD(ms,@WhileDur,0),114)

  PRINT ' '
  PRINT SPACE(3)
      + CASE
            WHEN @TallyDur < @WhileDur
            THEN 'fnCheckLuhn10 is faster by '
               + CAST(100.0 - (@TallyDur/@WhileDur)*100.0 AS VARCHAR(10)) 
               + '% Overall'
            WHEN @WhileDur < @TallyDur
            THEN 'fnIsLuhnValid is faster by '
               + CAST(100.0 - (@WhileDur/@TallyDur)*100.0 AS VARCHAR(10)) 
               + '% Overall'
            ELSE 'Both functions are identical in duration.'
        END

  PRINT ' '
  PRINT SPACE(3) + REPLICATE('** Final Stats **',5)


--Jeff Moden

Edited by - Jeff Moden on 12/31/2006 16:04:09
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29910 Posts

Posted - 12/31/2006 :  17:51:15  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Very impressive work Jeff!
The small difference of only 5 percent is almost negliable. Good to now that a permanent tally table can come in handy some times

Happy new Year to you in some 6 hours in NY and 9 hours in LA!
it is only 10 minutes left here in Sweden, so we are going outside now to rig our rockets and firecrackers...


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

Jeff Moden
Aged Yak Warrior

USA
649 Posts

Posted - 01/01/2007 :  16:19:09  Show Profile  Reply with Quote
Thanks and same to you, Peter. Happy New Year.

--Jeff Moden
Go to Top of Page

ja928
Starting Member

USA
5 Posts

Posted - 07/10/2007 :  09:50:59  Show Profile  Reply with Quote
Thanks for the excellent thread! I'm not doing the kind of volume you are discussing, but I wanted to suggest a small change to Peter's code for deriving the check digit. I don't think you need the multiplier variable. It's very clever and might be faster arithmetic to use 3 - @Multiplier, but I think the code reads a bit more like the Luhn logic using:

WHILE @Index >= 1
BEGIN
SELECT @Plus = ((@Index % 2) + 1) * CAST(SUBSTRING(@Luhn, @Index, 1) as tinyint),
@Sum = @Sum + (@Plus / 10) + (@Plus % 10), --Casting out nines
@Index = @Index -1
END

I see how it keeps the two procedures very similar, but by changing the initial value of multiplier. Either way, this was a big help.

Edited by - ja928 on 07/10/2007 10:00:04
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29910 Posts

Posted - 07/10/2007 :  12:07:16  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Did you try your code against the 8 million test table above?


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29910 Posts

Posted - 09/29/2008 :  10:31:12  Show Profile  Visit SwePeso's Homepage  Reply with Quote
A twice as fast approach on fixed sized string to check
CREATE FUNCTION dbo.fnIsValidSwedishSSN
(
	@SSN CHAR(10)
)
RETURNS BIT
AS
BEGIN
	RETURN	CASE
			WHEN @SSN LIKE '%[^0-9]%' THEN 0
			WHEN @SSN IS NULL THEN 0
			WHEN	(
					+ 2 * CAST(SUBSTRING(@SSN, 1, 1) AS TINYINT) / 10
					+ 2 * CAST(SUBSTRING(@SSN, 1, 1) AS TINYINT) % 10
					+ CAST(SUBSTRING(@SSN, 2, 1) AS TINYINT)
					+ 2 * CAST(SUBSTRING(@SSN, 3, 1) AS TINYINT) / 10
					+ 2 * CAST(SUBSTRING(@SSN, 3, 1) AS TINYINT) % 10
					+ CAST(SUBSTRING(@SSN, 4, 1) AS TINYINT)
					+ 2 * CAST(SUBSTRING(@SSN, 5, 1) AS TINYINT) / 10
					+ 2 * CAST(SUBSTRING(@SSN, 5, 1) AS TINYINT) % 10
					+ CAST(SUBSTRING(@SSN, 6, 1) AS TINYINT)
					+ 2 * CAST(SUBSTRING(@SSN, 7, 1) AS TINYINT) / 10
					+ 2 * CAST(SUBSTRING(@SSN, 7, 1) AS TINYINT) % 10
					+ CAST(SUBSTRING(@SSN, 8, 1) AS TINYINT)
					+ 2 * CAST(SUBSTRING(@SSN, 9, 1) AS TINYINT) / 10
					+ 2 * CAST(SUBSTRING(@SSN, 9, 1) AS TINYINT) % 10
					+ CAST(SUBSTRING(@SSN, 10, 1) AS TINYINT)
				) % 10 = 0 THEN 1
			ELSE 0
		END
END


E 12°55'05.63"
N 56°04'39.26"

Edited by - SwePeso on 09/29/2008 10:43:46
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29910 Posts

Posted - 09/29/2008 :  10:45:41  Show Profile  Visit SwePeso's Homepage  Reply with Quote
And for credit card (mastercard and visa)
CREATE FUNCTION dbo.fnIsValidCard
(
	@Card CHAR(16)
)
RETURNS TINYINT
AS
BEGIN
	RETURN	CASE
			WHEN @Card LIKE '%[^0-9]%' THEN 0
			WHEN @Card IS NULL THEN 0
			WHEN	(
					+ 2 * CAST(SUBSTRING(@Card, 1, 1) AS TINYINT) / 10
					+ 2 * CAST(SUBSTRING(@Card, 1, 1) AS TINYINT) % 10
					+ CAST(SUBSTRING(@Card, 2, 1) AS TINYINT)
					+ 2 * CAST(SUBSTRING(@Card, 3, 1) AS TINYINT) / 10
					+ 2 * CAST(SUBSTRING(@Card, 3, 1) AS TINYINT) % 10
					+ CAST(SUBSTRING(@Card, 4, 1) AS TINYINT)
					+ 2 * CAST(SUBSTRING(@Card, 5, 1) AS TINYINT) / 10
					+ 2 * CAST(SUBSTRING(@Card, 5, 1) AS TINYINT) % 10
					+ CAST(SUBSTRING(@Card, 6, 1) AS TINYINT)
					+ 2 * CAST(SUBSTRING(@Card, 7, 1) AS TINYINT) / 10
					+ 2 * CAST(SUBSTRING(@Card, 7, 1) AS TINYINT) % 10
					+ CAST(SUBSTRING(@Card, 8, 1) AS TINYINT)
					+ 2 * CAST(SUBSTRING(@Card, 9, 1) AS TINYINT) / 10
					+ 2 * CAST(SUBSTRING(@Card, 9, 1) AS TINYINT) % 10
					+ CAST(SUBSTRING(@Card, 10, 1) AS TINYINT)
					+ 2 * CAST(SUBSTRING(@Card, 11, 1) AS TINYINT) / 10
					+ 2 * CAST(SUBSTRING(@Card, 11, 1) AS TINYINT) % 10
					+ CAST(SUBSTRING(@Card, 12, 1) AS TINYINT)
					+ 2 * CAST(SUBSTRING(@Card, 13, 1) AS TINYINT) / 10
					+ 2 * CAST(SUBSTRING(@Card, 13, 1) AS TINYINT) % 10
					+ CAST(SUBSTRING(@Card, 14, 1) AS TINYINT)
					+ 2 * CAST(SUBSTRING(@Card, 15, 1) AS TINYINT) / 10
					+ 2 * CAST(SUBSTRING(@Card, 15, 1) AS TINYINT) % 10
					+ CAST(SUBSTRING(@Card, 16, 1) AS TINYINT)
				) % 10 = 0 THEN 1
			ELSE 0
		END
END



E 12°55'05.63"
N 56°04'39.26"
Go to Top of Page
Page: of 2 Previous Topic Topic Next Topic  
Next Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.28 seconds. Powered By: Snitz Forums 2000