SQL Server function to validate vehicle identification numbers (VIN) using length, format, and checksum rules.

UDF_VALIDATE_VIN.sql

CREATE FUNCTION dbo.UDF_VALIDATE_VIN(@VIN varchar(17))
RETURNS @RESULT TABLE 
(
    IS_VALID BIT NOT NULL,
	LENGTH_VALID BIT NOT NULL,
	FORMAT_VALID BIT NOT NULL,
	CHECKSUM_VALID BIT NOT NULL,
	CHECK_DIGIT CHAR(1) NULL,
	PRODUCT INT NULL,
	REMAINDER INT NULL
)
AS 
BEGIN
	
	DECLARE @VALID BIT, @LENGTH_VALID BIT, @FORMAT_VALID BIT, @CHECKSUM_VALID BIT, @CHECK_DIGIT CHAR(1), @PRODUCT INT, @REMAINDER INT, @TRANSLITERATED VARCHAR(17), @INDEX INT, @INT_VALUE INT;
	DECLARE @WEIGTHS TABLE ([INDEX] INT NOT NULL, [WEIGHT] INT NOT NULL);
	
	INSERT @WEIGTHS ([INDEX], [WEIGHT])
	SELECT 1, 8	
	UNION SELECT 2, 7	
	UNION SELECT 3, 6	
	UNION SELECT 4, 5	
	UNION SELECT 5, 4	
	UNION SELECT 6, 3	
	UNION SELECT 7, 2	
	UNION SELECT 8, 10	
	UNION SELECT 9, 0	
	UNION SELECT 10, 9	
	UNION SELECT 11, 8	
	UNION SELECT 12, 7	
	UNION SELECT 13, 6	
	UNION SELECT 14, 5	
	UNION SELECT 15, 4	
	UNION SELECT 16, 3	
	UNION SELECT 17, 2
		
		


	SET @INDEX = 1
	SET @PRODUCT = 0
	SET @REMAINDER = 0
	set @VIN = UPPER(RTRIM(LTRIM(@VIN)))

	IF(LEN(@VIN) = 17)
		SET @LENGTH_VALID = 1
	ELSE 
		SET @LENGTH_VALID = 0

	IF(CHARINDEX('I', @VIN) > 0 OR CHARINDEX('Q', @VIN) > 0 OR CHARINDEX('O', @VIN) > 0)
		SET @FORMAT_VALID = 0
	ELSE 
		SET @FORMAT_VALID = 1

	--Checksum cannot be valid if either length or format is invalid
	IF(@LENGTH_VALID = 1 AND @FORMAT_VALID = 1)
	BEGIN

		SET @CHECK_DIGIT = SUBSTRING(@VIN, 9, 1)

		SET @TRANSLITERATED = REPLACE(@VIN, 'A', 1);
		SET @TRANSLITERATED = REPLACE(@TRANSLITERATED, 'B', 2);
		SET @TRANSLITERATED = REPLACE(@TRANSLITERATED, 'C', 3);
		SET @TRANSLITERATED = REPLACE(@TRANSLITERATED, 'D', 4);
		SET @TRANSLITERATED = REPLACE(@TRANSLITERATED, 'E', 5);
		SET @TRANSLITERATED = REPLACE(@TRANSLITERATED, 'F', 6);
		SET @TRANSLITERATED = REPLACE(@TRANSLITERATED, 'G', 7);
		SET @TRANSLITERATED = REPLACE(@TRANSLITERATED, 'H', 8);
		SET @TRANSLITERATED = REPLACE(@TRANSLITERATED, 'J', 1);
		SET @TRANSLITERATED = REPLACE(@TRANSLITERATED, 'K', 2);
		SET @TRANSLITERATED = REPLACE(@TRANSLITERATED, 'L', 3);
		SET @TRANSLITERATED = REPLACE(@TRANSLITERATED, 'M', 4);
		SET @TRANSLITERATED = REPLACE(@TRANSLITERATED, 'N', 5);
		SET @TRANSLITERATED = REPLACE(@TRANSLITERATED, 'P', 7);
		SET @TRANSLITERATED = REPLACE(@TRANSLITERATED, 'R', 9);
		SET @TRANSLITERATED = REPLACE(@TRANSLITERATED, 'S', 2);
		SET @TRANSLITERATED = REPLACE(@TRANSLITERATED, 'T', 3);
		SET @TRANSLITERATED = REPLACE(@TRANSLITERATED, 'U', 4);
		SET @TRANSLITERATED = REPLACE(@TRANSLITERATED, 'V', 5);
		SET @TRANSLITERATED = REPLACE(@TRANSLITERATED, 'W', 6);
		SET @TRANSLITERATED = REPLACE(@TRANSLITERATED, 'X', 7);
		SET @TRANSLITERATED = REPLACE(@TRANSLITERATED, 'Y', 8);
		SET @TRANSLITERATED = REPLACE(@TRANSLITERATED, 'Z', 9);

		WHILE(@INDEX < 18)
		BEGIN
			SET @INT_VALUE = CAST(SUBSTRING(@TRANSLITERATED, @INDEX, 1) AS INT)	
			SELECT @PRODUCT = @INT_VALUE * [WEIGHT] + @PRODUCT FROM @WEIGTHS WHERE [INDEX] = @INDEX
			SET @INDEX = @INDEX + 1
		END

		SET @REMAINDER = @PRODUCT % 11
		IF((@REMAINDER = 10 AND @CHECK_DIGIT = 'X') OR (ISNUMERIC(@CHECK_DIGIT) = 1 AND @REMAINDER = CAST(@CHECK_DIGIT AS INT)))
			SET @CHECKSUM_VALID = 1
		ELSE 
			SET @CHECKSUM_VALID = 0
	END
	ELSE
		SET @CHECKSUM_VALID = 0


	IF(@LENGTH_VALID = 1 AND @FORMAT_VALID = 1 AND @CHECKSUM_VALID = 1)
		SET @VALID = 1
	ELSE 
		SET @VALID = 0

	INSERT INTO @RESULT (IS_VALID, LENGTH_VALID, FORMAT_VALID, CHECKSUM_VALID, CHECK_DIGIT, PRODUCT, REMAINDER) 
				 VALUES (@VALID, @LENGTH_VALID, @FORMAT_VALID, @CHECKSUM_VALID, @CHECK_DIGIT, @PRODUCT, @REMAINDER)
	
	RETURN
END

Thank you sir. This helps my application a lot on not wasting time trying to decode invalid VINs.