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.
| 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 digitCREATE FUNCTION dbo.fnIsLuhnValid( @Luhn VARCHAR(8000))RETURNS BITASBEGIN 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 ENDEND Peter LarssonHelsingborg, Sweden |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2006-12-11 : 08:34:06
|
Use this to add the Luhn character check digitCREATE FUNCTION dbo.fnGetLuhn( @Luhn VARCHAR(7999))RETURNS VARCHAR(8000)ASBEGIN 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) ENDEND Peter LarssonHelsingborg, Sweden |
 |
|
|
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 |
|
|
|
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 LarssonHelsingborg, Sweden |
|
|
|
rockmoose
SQL Natt Alfen
3279 Posts |
Posted - 2006-12-11 : 15:34:12
|
| Ok, thanks Peter! |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2006-12-11 : 17:20:35
|
quote:
Originally posted by Peso
See more herehttp://en.wikipedia.org/wiki/Luhn_algorithmPeter LarssonHelsingborg, Sweden
Are you going to edit the Wikipedia article to add an External Link to this script?CODO ERGO SUM |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2006-12-11 : 17:22:42
|
| Can I do that?Peter LarssonHelsingborg, Sweden |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2006-12-11 : 17:25:15
|
| Cool!Thanks Michael.Peter LarssonHelsingborg, Sweden |
|
|
|
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 fnCheckLuhn10GOCREATE 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 variablesDECLARE @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)--===================================================================================================================== ENDAnd, 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 |
|
|
|
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 LarssonHelsingborg, Sweden |
|
|
|
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 |
|
|
|
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 LarssonHelsingborg, Sweden |
|
|
|
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 |
|
|
|
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=76195go--=======================================================================================-- 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 pictureDECLARE @StartTime DATETIME --To measure simple duration of each runDECLARE @StopTime DATETIME --To measure simple duration of each runDECLARE @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 counterDECLARE @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 |
|
|
|
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 LarssonHelsingborg, Sweden |
|
|
|
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 |
|
|
|
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 ENDI see how it keeps the two procedures very similar, but by changing the initial value of multiplier. Either way, this was a big help. |
|
|
|
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 LarssonHelsingborg, Sweden |
|
|
|
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 checkCREATE FUNCTION dbo.fnIsValidSwedishSSN( @SSN CHAR(10))RETURNS BITASBEGIN 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 ENDEND
E 12°55'05.63"N 56°04'39.26" |
|
|
|
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 TINYINTASBEGIN 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 ENDEND
E 12°55'05.63"N 56°04'39.26" |
|
|
|
Next Page
|
|
|
|
|
Luhn algorithm