Skip to content

Instantly share code, notes, and snippets.

@chadbaldwin
Created October 12, 2021 22:14
Show Gist options
  • Save chadbaldwin/2b8f62cdd06365044c64fa016a02537f to your computer and use it in GitHub Desktop.
Save chadbaldwin/2b8f62cdd06365044c64fa016a02537f to your computer and use it in GitHub Desktop.
------------------------------------------------------------------------------
------------------------------------------------------------------------------
-- 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')
@chadbaldwin
Copy link
Author

[reserving first comment]

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment