Need Help Identifying Credit Card Numbers in your databases?
Do you work in a PCI environment? If you need to identify payment data within your databases this function can be used to help you do so. The function accepts a BigInt datatype representing a credit card number and will run it through a LUHN function to determine whether it is a valid credit card number.
Here is a good reference if you need to port the formula to another language:
https://www.ibm.com/docs/en/order-management-sw/9.3.0?topic=cpms-handling-credit-cards
/* This code is offered as proof of concept code. User is responsible for testing within their own environment. No warranty provided. */ Create Function dbo.FNLuhnChk(@number bigint) returns varchar(10) AS BEGIN Declare @strEval varchar(20) Declare @strEvalNew varchar(20) Declare @stringLen int Declare @doubled int Declare @evenFromRight int SET @strEval=convert(varchar(20),@number) SET @strEvalNew=@strEval DECLARE @tmpChkSum Table ( id int identity(1,1), orig int, calc varchar(2), nonCalc int ) SET @stringLen=len(@strEvalNew) While @stringLen>=1 BEGIN SET @evenFromRight=SUBSTRING(@strEvalNew,@stringLen-1,1) if @stringLen>=2 BEGIN insert into @tmpChkSum(orig,calc,nonCalc) values(SUBSTRING(@strEvalNew,@stringLen,1),SUBSTRING(@strEvalNew,@stringLen,1),1) --The Odd Numbers set @doubled=2*@evenFromRight --the even Numbers are doubled if (@doubled>=10) --digits are added if >9 BEGIN --select right(@doubled,1), left(@doubled,1),convert(int,right(@doubled,1))+convert(int,left(@doubled,1)) set @doubled=convert(int,convert(varchar,right(@doubled,1))) + convert(int,convert(varchar,left(@doubled,1))) END insert into @tmpChkSum(orig,calc) values(@evenFromRight,@doubled) -- even numbers are added to the table SET @strEvalNew=left(@strEvalNew,@stringLen-2) END ELSE BEGIN insert into @tmpChkSum(orig,calc,nonCalc) values(SUBSTRING(@strEvalNew,@stringLen,1),SUBSTRING(@strEvalNew,@stringLen,1),1) SET @strEvalNew=left(@strEvalNew,@stringLen-1) END SET @stringLen=len(@strEvalNew) END DECLARE @isValid varchar(10) Select @isValid=CASE sum(convert(int,calc))%10 when 0 then 'CC' ELSE 'NOT CC' END from @tmpChkSum return @isValid End GO /**TEST IT*// select dbo.FNLuhnChk(convert(bigint,'44412345678954321'))