VIN_Check.sql

------------------------------------------------------------------------------

------------------------------------------------------------------------------
--	DECLARE @VIN VARCHAR(17) = '3FA6PQHR5DR24649O' --VIN with invalid characters test
--	DECLARE @VIN VARCHAR(17) = '3FA6P0HR5DR246490' --VIN with invalid check digit test
	DECLARE @VIN VARCHAR(17) = '1FTFX1E58LKF43489'; --Valid VIN test

DECLARE @VINCheck TABLE (Letter CHAR(1) NOT NULL, [Value] TINYINT NOT NULL);
INSERT INTO @VINCheck
	VALUES	('A',1),('B',2),('C',3),('D',4),('E',5),('F',6),('G',7),('H',8),
			('J',1),('K',2),('L',3),('M',4),('N',5)        ,('P',7)        ,('R',9)
			       ,('S',2),('T',3),('U',4),('V',5),('W',6),('X',7),('Y',8),('Z',9);

SELECT OriginalVIN		= v.VIN
	, VINBreakout		= x.nVIN
	, CurrentCheckDigit	= SUBSTRING(v.VIN, 9, 1)
	, CorrectCheckDigit	= y.CorrectCheckDigit
	, IsValidVIN		= IIF(SUBSTRING(v.VIN, 9, 1) = y.CorrectCheckDigit, 1, 0)
FROM (SELECT VIN = @VIN) v
	CROSS APPLY (SELECT STUFF(STUFF(v.VIN,11,0,'   '),9,1,'_')) x(nVIN)
	CROSS APPLY (
		SELECT (
			(IIF(SUBSTRING(v.VIN,1 ,1) LIKE '[0-9]', SUBSTRING(v.VIN,1 ,1), (SELECT vc.[Value] FROM @VINCheck vc WHERE vc.Letter = SUBSTRING(v.VIN,1 ,1))) * 8 ) +
			(IIF(SUBSTRING(v.VIN,2 ,1) LIKE '[0-9]', SUBSTRING(v.VIN,2 ,1), (SELECT vc.[Value] FROM @VINCheck vc WHERE vc.Letter = SUBSTRING(v.VIN,2 ,1))) * 7 ) +
			(IIF(SUBSTRING(v.VIN,3 ,1) LIKE '[0-9]', SUBSTRING(v.VIN,3 ,1), (SELECT vc.[Value] FROM @VINCheck vc WHERE vc.Letter = SUBSTRING(v.VIN,3 ,1))) * 6 ) +
			(IIF(SUBSTRING(v.VIN,4 ,1) LIKE '[0-9]', SUBSTRING(v.VIN,4 ,1), (SELECT vc.[Value] FROM @VINCheck vc WHERE vc.Letter = SUBSTRING(v.VIN,4 ,1))) * 5 ) +
			(IIF(SUBSTRING(v.VIN,5 ,1) LIKE '[0-9]', SUBSTRING(v.VIN,5 ,1), (SELECT vc.[Value] FROM @VINCheck vc WHERE vc.Letter = SUBSTRING(v.VIN,5 ,1))) * 4 ) +
			(IIF(SUBSTRING(v.VIN,6 ,1) LIKE '[0-9]', SUBSTRING(v.VIN,6 ,1), (SELECT vc.[Value] FROM @VINCheck vc WHERE vc.Letter = SUBSTRING(v.VIN,6 ,1))) * 3 ) +
			(IIF(SUBSTRING(v.VIN,7 ,1) LIKE '[0-9]', SUBSTRING(v.VIN,7 ,1), (SELECT vc.[Value] FROM @VINCheck vc WHERE vc.Letter = SUBSTRING(v.VIN,7 ,1))) * 2 ) +
			(IIF(SUBSTRING(v.VIN,8 ,1) LIKE '[0-9]', SUBSTRING(v.VIN,8 ,1), (SELECT vc.[Value] FROM @VINCheck vc WHERE vc.Letter = SUBSTRING(v.VIN,8 ,1))) * 10) +
			(IIF(SUBSTRING(v.VIN,10,1) LIKE '[0-9]', SUBSTRING(v.VIN,10,1), (SELECT vc.[Value] FROM @VINCheck vc WHERE vc.Letter = SUBSTRING(v.VIN,10,1))) * 9 ) +
			(IIF(SUBSTRING(v.VIN,11,1) LIKE '[0-9]', SUBSTRING(v.VIN,11,1), (SELECT vc.[Value] FROM @VINCheck vc WHERE vc.Letter = SUBSTRING(v.VIN,11,1))) * 8 ) +
			(IIF(SUBSTRING(v.VIN,12,1) LIKE '[0-9]', SUBSTRING(v.VIN,12,1), (SELECT vc.[Value] FROM @VINCheck vc WHERE vc.Letter = SUBSTRING(v.VIN,12,1))) * 7 ) +
			(IIF(SUBSTRING(v.VIN,13,1) LIKE '[0-9]', SUBSTRING(v.VIN,13,1), (SELECT vc.[Value] FROM @VINCheck vc WHERE vc.Letter = SUBSTRING(v.VIN,13,1))) * 6 ) +
			(IIF(SUBSTRING(v.VIN,14,1) LIKE '[0-9]', SUBSTRING(v.VIN,14,1), (SELECT vc.[Value] FROM @VINCheck vc WHERE vc.Letter = SUBSTRING(v.VIN,14,1))) * 5 ) +
			(IIF(SUBSTRING(v.VIN,15,1) LIKE '[0-9]', SUBSTRING(v.VIN,15,1), (SELECT vc.[Value] FROM @VINCheck vc WHERE vc.Letter = SUBSTRING(v.VIN,15,1))) * 4 ) +
			(IIF(SUBSTRING(v.VIN,16,1) LIKE '[0-9]', SUBSTRING(v.VIN,16,1), (SELECT vc.[Value] FROM @VINCheck vc WHERE vc.Letter = SUBSTRING(v.VIN,16,1))) * 3 ) +
			(IIF(SUBSTRING(v.VIN,17,1) LIKE '[0-9]', SUBSTRING(v.VIN,17,1), (SELECT vc.[Value] FROM @VINCheck vc WHERE vc.Letter = SUBSTRING(v.VIN,17,1))) * 2 )
		) % 11 AS ChkDigit
	) AS c
	CROSS APPLY (SELECT CorrectCheckDigit = IIF(c.ChkDigit = 10, 'X', CONVERT(CHAR(1), c.ChkDigit))) y;
------------------------------------------------------------------------------

------------------------------------------------------------------------------
GO
------------------------------------------------------------------------------
DECLARE @VIN VARCHAR(17) = 'JKAEXEC1X9DA37076' --Valid VIN test
​
DECLARE @CheckDigit char(1);
​
IF (@VIN NOT LIKE '%[^0-9A-HJ-NPR-Z]%')
BEGIN
	SELECT @CheckDigit = REPLACE(CONVERT(char(2), SUM(z.CharValue * CHOOSE(t.Position, 8,7,6,5,4,3,2,10,0,9,8,7,6,5,4,3,2)) % 11),'10','X')
	FROM (VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15),(16),(17)) t(Position)
		CROSS APPLY (SELECT CurrentChar = CONVERT(char(1), UPPER(SUBSTRING(@VIN, t.Position, 1)))) x
																		--A B C D E F G H I J K L M N O P Q R S T U V W X Y Z
		CROSS APPLY (SELECT LetterValue = CHOOSE(ASCII(x.CurrentChar)-64, 1,2,3,4,5,6,7,8,0,1,2,3,4,5,0,7,0,9,2,3,4,5,6,7,8,9)) y
		CROSS APPLY (SELECT CharValue = IIF(x.CurrentChar LIKE '[0-9]', x.CurrentChar, y.LetterValue)) z
END;
​
SELECT OriginalVIN		= @VIN
	, VINBreakout		= STUFF(STUFF(@VIN,11,0,'   '),9,1,'_')
	, CurrentCheckDigit	= SUBSTRING(@VIN, 9, 1)
	, CorrectCheckDigit	= @CheckDigit
	, IsValidVIN		= IIF(SUBSTRING(@VIN, 9, 1) = @CheckDigit, 1, 0)
------------------------------------------------------------------------------

------------------------------------------------------------------------------
GO
------------------------------------------------------------------------------
--	DECLARE @VIN VARCHAR(17) = '3FA6PQHR5DR24649O'; --VIN with invalid characters test
--	DECLARE @VIN VARCHAR(17) = '3FA6P0HR5DR246490' --VIN with invalid check digit test
DECLARE @VIN VARCHAR(17) = 'JKAEXEC1X9DA37076' --Valid VIN test

DECLARE @CheckDigit tinyint, @CheckDigitChar char(1);

IF (@VIN NOT LIKE '%[^0-9A-HJ-NPR-Z]%')
BEGIN
	SELECT @CheckDigit = SUM(CHOOSE(ASCII(UPPER(SUBSTRING(@VIN, t.Pos, 1)))-47, 0,1,2,3,4,5,6,7,8,9,0,0,0,0,0,0,0,1,2,3,4,5,6,7,8,0,1,2,3,4,5,0,7,0,9,2,3,4,5,6,7,8,9) * CHOOSE(t.Pos, 8,7,6,5,4,3,2,10,0,9,8,7,6,5,4,3,2)) % 11
	FROM (VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15),(16),(17)) t(Pos)

	SET @CheckDigitChar = IIF(@CheckDigit = 10, 'X', CONVERT(char(1), @CheckDigit))
END;

SELECT OriginalVIN		= @VIN
	, VINBreakout		= STUFF(STUFF(@VIN,11,0,'   '),9,1,'_')
	, CurrentCheckDigit	= SUBSTRING(@VIN, 9, 1)
	, CorrectCheckDigit	= @CheckDigitChar
	, IsValidVIN		= IIF(SUBSTRING(@VIN, 9, 1) = @CheckDigitChar, 1, 0)
------------------------------------------------------------------------------

------------------------------------------------------------------------------
SELECT TRANSLATE('B', 1), 'ABCDEFGHIJKLMNOPQRSTUVWXYZ', '12345678012345070923456789')

[reserving first comment]