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'))