USE [CustomerServiceDB] GO /****** Object: UserDefinedFunction [dbo].[fnIsLuhnValid] Script Date: 03/05/2015 13:46:39 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE FUNCTION [dbo].[fnIsLuhnValid] ( @Luhn VARCHAR(max) ) 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 GO