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
 General SQL Server Forums
 Script Library
 Luhn algorithm

Author  Topic 

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-11 : 08:19:11
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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-11 : 08:34:06
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
Go to Top of Page

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2006-12-11 : 15:08:02
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

30421 Posts

Posted - 2006-12-11 : 15:26:13
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

3279 Posts

Posted - 2006-12-11 : 15:34:12
Ok, thanks Peter!
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-11 : 15:54:39
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)

7020 Posts

Posted - 2006-12-11 : 17:20:35
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

30421 Posts

Posted - 2006-12-11 : 17:22:42
Can I do that?


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-11 : 17:25:15
Cool!
Thanks Michael.


Peter Larsson
Helsingborg, Sweden
Go to Top of Page

Jeff Moden
Aged Yak Warrior

652 Posts

Posted - 2006-12-28 : 17:29:45
@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
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-31 : 07:51:43
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

652 Posts

Posted - 2006-12-31 : 08:53:55
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
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-31 : 12:02:53
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
Go to Top of Page

Jeff Moden
Aged Yak Warrior

652 Posts

Posted - 2006-12-31 : 14:37:06
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
Go to Top of Page

Jeff Moden
Aged Yak Warrior

652 Posts

Posted - 2006-12-31 : 16:03:27
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
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-31 : 17:51:15
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

652 Posts

Posted - 2007-01-01 : 16:19:09
Thanks and same to you, Peter. Happy New Year.

--Jeff Moden
Go to Top of Page

ja928
Starting Member

5 Posts

Posted - 2007-07-10 : 09:50:59
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.
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-07-10 : 12:07:16
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

30421 Posts

Posted - 2008-09-29 : 10:31:12
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"
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-29 : 10:45:41
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
    Next Page

- Advertisement -