Vehicle Identification Number (VIN) Validator for #SQLServer

Posted on by

#DWH #BI #Automotive

In my last article, I focused on Vehicle Identification Numbers (VINs), about their structure and how to verify them in a data warehouse setting. This article focuses on an implementation of a VIN Validator for Microsoft SQL Server. The validator implements all the checks discussed in the earlier article. The downloadable source code can be found at the end of this article.

Regular Expressions

The VIN Validator depends on some regular expressions which are not supported natively by Microsoft SQL Server. Therefore, the next sections address how to add support for regular expressions before we can focus on the core VIN validation routines.

Adding the Assembly

Luckily, there is a Open Source project available on CodeProject that adds support for Regular Expressions in Microsoft SQL Server 2005 to 2008, and it also works with Microsoft SQL Server 2012.

Before you can use .NET assemblies in your database, the administrator needs to allow the feature using the following script:

sp_configure 'clr enabled', 1
GO
RECONFIGURE
GO

The feature can be deactivated using:

sp_configure 'clr enabled', 0
GO
RECONFIGURE
GO

As an alternative for downloading the assembly and registering it on your own, you can also use the following source code to register the assembly:

CREATE ASSEMBLY [SqlRegularExpressions]
FROM 0x4D5A90000300000004000000FFFF0000B800000000000000400000000000000000000000000000000000000000000000000000000000000000000000800000000E1FBA0E00B409CD21B8014CCD21546869732070726F6772616D2063616E6E6F742062652072756E20696E20444F53206D6F64652E0D0D0A2400000000000000504500004C010300C020D24A0000000000000000E00002210B0108000010000000200000000000007E2900000020000000400000000040000020000000100000040000000000000004000000000000000080000000100000000000000300408500001000001000000000100000100000000000001000000000000000000000002C2900004F00000000400000F003000000000000000000000000000000000000006000000C00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000200000080000000000000000000000082000004800000000000000000000002E7465787400000084090000002000000010000000100000000000000000000000000000200000602E72737263000000F0030000004000000010000000200000000000000000000000000000400000402E72656C6F6300000C000000006000000010000000300000000000000000000000000000400000420000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000006029000000000000480000000200050080210000AC07000001000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000001B30030074000000010000110F00280E00000A2C067E0F00000A2A0F01280E00000A2C0C7E1000000A281100000A10010F02281200000A2C0816281300000A100202281400000A03281400000A04281500000A281600000A0A066F1700000A7E1000000A281800000A2C087E1900000A0BDE0BDE0326FE1A7E1A00000A2A072A011000000000350034690003010000011B3003007A000000020000110F00280E00000A2C067E1B00000A2A0F01280E00000A2C0C7E1000000A281100000A10010F02281200000A2C0816281300000A100202281400000A03281400000A04281500000A281600000A0A066F1700000A7E1000000A281800000A2C0E066F1700000A731C00000A0BDE0BDE0326FE1A7E1B00000A2A072A000001100000000035003A6F0003010000011E02281D00000A2A42534A4201000100000000000C00000076322E302E35303732370000000005006C00000088020000237E0000F40200003403000023537472696E67730000000028060000080000002355530030060000100000002347554944000000400600006C01000023426C6F620000000000000002000001471502000900000000FA01330016000001000000160000000200000003000000060000001D0000000C00000002000000010000000300000000000A000100000000000600490042000A0071005C000A007C005C000A0086005C000600C000AE000600F800D90006000C01AE0006002501AE0006004001AE0006005B01AE0006007401AE0006008D01AE000600AC01AE000600C901AE0006000002E00106002002E0010A006F0254020600AA0242000E00EE02CF020E00F402CF020E00FA02CF020E000703CF020000000001000000000001000100010010002400000005000100010050200000000096008F000A000100E0200000000096009A00150004007821000000008618A80020000700000001008402000002008A02000003009202000001008402000002008A020000030092022900A80024003100A80029003900A80024004100A80024004900A80024005100A80024005900A80024006100A80024006900A80024007100A80024007900A8002E008100A80020008900A800200019009A026A001100A5026E009100B10272001900B702750021009A026A002100B7027B001900C30281002100C30287009900F4028D00B1000F039600910019039A00110027036E0011002C036E001900A502E1001900A80024000900A800200020006B0033002E00330016012E001300EC002E002300EC002E002B00F2002E004B00EC002E0063004C012E004300EC002E003B0031012E00530016012E005B00430140006B00A700A000E5000480000002000000F30D708E0000000000003E02000002000000000000000000000001003900000000000200000000000000000000000100500000000000020000000000000000000000010042000000000000000000003C4D6F64756C653E0053716C526567756C617245787072657373696F6E732E646C6C0055736572446566696E656446756E6374696F6E73006D73636F726C69620053797374656D004F626A6563740053797374656D2E446174610053797374656D2E446174612E53716C54797065730053716C426F6F6C65616E0053716C537472696E670053716C496E7433320052656765784D617463680052656765785374724D61746368002E63746F720053797374656D2E5265666C656374696F6E00417373656D626C7956657273696F6E4174747269627574650053797374656D2E52756E74696D652E496E7465726F70536572766963657300436F6D56697369626C6541747472696275746500417373656D626C7943756C7475726541747472696275746500417373656D626C7954726164656D61726B41747472696275746500417373656D626C79436F7079726967687441747472696275746500417373656D626C7950726F6475637441747472696275746500417373656D626C79436F6D70616E7941747472696275746500417373656D626C79436F6E66696775726174696F6E41747472696275746500417373656D626C794465736372697074696F6E41747472696275746500417373656D626C795469746C654174747269627574650053797374656D2E52756E74696D652E436F6D70696C6572536572766963657300436F6D70696C6174696F6E52656C61786174696F6E734174747269627574650052756E74696D65436F6D7061746962696C6974794174747269627574650053716C526567756C617245787072657373696F6E73004D6963726F736F66742E53716C5365727665722E5365727665720053716C46756E6374696F6E41747472696275746500696E707574007061747465726E006F7074696F6E73006765745F49734E756C6C004E756C6C00537472696E6700456D707479006F705F496D706C69636974006F705F4578706C696369740053797374656D2E546578742E526567756C617245787072657373696F6E73005265676578004D617463680052656765784F7074696F6E730043617074757265006765745F56616C7565006F705F496E657175616C69747900547275650046616C736500000000032000000000006B9AFC314597B54492ED16463E6218A40008B77A5C561934E0890A00031109110D110D11110A0003110D110D110D111103200001042001010E042001010204200101083601000300540E044E616D650A52656765784D6174636854020F497344657465726D696E69737469630154020949735072656369736501032000020306110902060E050001110D0E0500011111080500010E110D05000108111108000312510E0E11550320000E050002020E0E060702125111093901000300540E044E616D650D52656765785374724D6174636854020F497344657465726D696E697374696301540209497350726563697365010306110D0607021251110D0501000000002301001E436F7079726967687420C2A9204D616A6F72206C6561677565203230303900001A01001553716C526567756C617245787072657373696F6E7300001101000C4D616A6F72206C656167756500000801000800000000001E01000100540216577261704E6F6E457863657074696F6E5468726F777301005429000000000000000000006E29000000200000000000000000000000000000000000000000000060290000000000000000000000005F436F72446C6C4D61696E006D73636F7265652E646C6C0000000000FF25002040000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000100100000001800008000000000000000000000000000000100010000003000008000000000000000000000000000000100000000004800000058400000940300000000000000000000940334000000560053005F00560045005200530049004F004E005F0049004E0046004F0000000000BD04EFFE0000010000000200708EF30D00000200708EF30D3F000000000000000400000002000000000000000000000000000000440000000100560061007200460069006C00650049006E0066006F00000000002400040000005400720061006E0073006C006100740069006F006E00000000000000B004F4020000010053007400720069006E006700460069006C00650049006E0066006F000000D002000001003000300030003000300034006200300000003C000D00010043006F006D00700061006E0079004E0061006D006500000000004D0061006A006F00720020006C006500610067007500650000000000540016000100460069006C0065004400650073006300720069007000740069006F006E0000000000530071006C0052006500670075006C0061007200450078007000720065007300730069006F006E007300000040000F000100460069006C006500560065007200730069006F006E000000000032002E0030002E0033003500370031002E00330036003400360034000000000054001A00010049006E007400650072006E0061006C004E0061006D0065000000530071006C0052006500670075006C0061007200450078007000720065007300730069006F006E0073002E0064006C006C00000060001E0001004C006500670061006C0043006F007000790072006900670068007400000043006F0070007900720069006700680074002000A90020004D0061006A006F00720020006C00650061006700750065002000320030003000390000005C001A0001004F0072006900670069006E0061006C00460069006C0065006E0061006D0065000000530071006C0052006500670075006C0061007200450078007000720065007300730069006F006E0073002E0064006C006C0000004C0016000100500072006F0064007500630074004E0061006D00650000000000530071006C0052006500670075006C0061007200450078007000720065007300730069006F006E007300000044000F000100500072006F006400750063007400560065007200730069006F006E00000032002E0030002E0033003500370031002E00330036003400360034000000000048000F00010041007300730065006D0062006C0079002000560065007200730069006F006E00000032002E0030002E0033003500370031002E0033003600340036003400000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000002000000C0000008039000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000

Registering the Function

The next step is to register the required function for matching regular expressions against the VIN:

CREATE FUNCTION [dbo].[RegexMatch](
  @Text [nvarchar](max), 
  @Pattern [nvarchar](255), @Options [int]
)
RETURNS [bit] WITH EXECUTE AS CALLER
AS 
EXTERNAL NAME [SqlRegularExpressions].[UserDefinedFunctions].[RegexMatch]
GO

The VIN Validator

Once we have added support for Regular Expressions to Microsoft SQL Server, we can focus on the VIN Validator itself.

The function will have one parameter (the VIN) and return an integer value with the following meanings:

  • 0: if the VIN is correct and all checks passed
  • 1: if it is not possible to verify the check digit, but everything else looks good. This is not an error because many manufacturers are not using the check digit.
  • 2: if the check digit is invalid. This is not necessarily an error. Check out my earlier post for more details. You have to decide how you deal with this return code (e.g., log a warning).
  • -1: if the length of the VIN is invalid
  • -2: if the format of the VIN is invalid
  • -3: if the WMI (manufacturer) is unknown

That results in the following function body:

ALTER FUNCTION [dbo].[ufnCheckVIN] 
(
    @VIN nvarchar(17)
)
RETURNS int
AS
BEGIN
    -- The return variable
    DECLARE @Result int;
 
    -- uppercase VIN
    SET @VIN = UPPER(@VIN);
 
    [...]
 
    -- Return the result of the function
    RETURN @Result;
END

Check 1: String Length

The first check is the simplest: checking the string length of the VIN. It must be exactly 17 characters long:

    -- Check 1: String Length
    SET @Result = IIF(LEN(LTRIM(RTRIM(@VIN))) = 17, 0, -1);

Check 2: VIN Format using Regular Expression

The second check verifies the format of the VIN. Only specific characters are valid at specific positions of the VIN. This check is performed using the first Regular Expression:

    -- Check 2: VIN Format using Regular Expression
    IF (@Result = 0)
        SET @Result = IIF([dbo].[RegexMatch](@VIN, 
            '^(([a-h,A-H,j-n,J-N,p-z,P-Z,0-9]{9})([a-h,A-H,j-n,J-N,p,P,r-t,R-T,v-z,V-Z,0-9])([a-h,A-H,j-n,J-N,p-z,P-Z,0-9])(d{6}))$', 1) = 1, 0, -2);

Check 3: World Manufacturer Identifier

The third check verifies that the WMI code at the beginning of the VIN is known. To achieve this, all assigned WMI codes have been loaded to a database table. The VIN Validator performs a lookup against this table to check if the WMI is valid:

    -- Check 3: World Manufacturer Identifier
    IF (@Result = 0)
        SET @Result = IIF(EXISTS (SELECT RecID FROM Manufacturers 
            WHERE WMI = SUBSTRING(@VIN, 1, 3)), 0, -3);

Check 4: VIN Check Digit (Part 1)

The fourth check is divided into two parts. The first part of the check verifies that the manufacturer is using check digits (not all manufacturers are using the check digit). This is done by using a regular expression from another table which contains exceptions to the check digit application. In addition to some manufacturers who do not use check digits, some other manufacturers, such as Lamborghini, have used wrong check digits in some of their models. These exceptions are defined in the table.

To achieve this, we use a cursor on the IgnorePatterns table which holds the exceptions:

    DECLARE PatternCursor CURSOR FOR
    SELECT [dbo].[RegexMatch](@VIN, Pattern, 1) FROM IgnorePatterns;

The code below is loading matching one pattern after the other from the IgnorePatterns table against the VIN. The while loop aborts when one of the pattern is returning a positive result (that means that the check digit should be ignored):

    -- Check 4: VIN Check Digit
    -- Part 1: Check if the check digit is valid and if there are any exceptions defined in [IgnorePatterns]
    IF (@Result = 0)
    BEGIN
        OPEN PatternCursor;
        FETCH NEXT FROM PatternCursor INTO @MatchResult;
 
        IF (@MatchResult = 0)
        BEGIN
            WHILE @@FETCH_STATUS = 0
            BEGIN
                FETCH NEXT FROM PatternCursor INTO @MatchResult;
                IF (@MatchResult = 1)
                BEGIN
                    SET @Result = 1;
                    BREAK;
                END
            END;
        END
        ELSE
            SET @Result = 1;
 
        CLOSE PatternCursor;
        DEALLOCATE PatternCursor;
    END;

Check 4: VIN Check Digit (Part 2)

Once we have verified that the manufacturer is using check digits, its time to validate the check digit itself. This is performed using IBM’s EBCDIC method which is implemented as a user defined function for Microsoft SQL Server:

CREATE FUNCTION [dbo].[ufnEBCDIC] 
(
    @Text nvarchar(MAX)
)
RETURNS nvarchar(MAX)
AS
BEGIN
    DECLARE @Result nvarchar(MAX)
 
    SET @Result = UPPER(@Text); 
    SET @Result = REPLACE(@Result, 'A', 1);
    SET @Result = REPLACE(@Result, 'B', 2);
    SET @Result = REPLACE(@Result, 'C', 3);
    SET @Result = REPLACE(@Result, 'D', 4);
    SET @Result = REPLACE(@Result, 'E', 5);
    SET @Result = REPLACE(@Result, 'F', 6);
    SET @Result = REPLACE(@Result, 'G', 7);
    SET @Result = REPLACE(@Result, 'H', 8);
    SET @Result = REPLACE(@Result, 'J', 1);
    SET @Result = REPLACE(@Result, 'K', 2);
    SET @Result = REPLACE(@Result, 'L', 3);
    SET @Result = REPLACE(@Result, 'M', 4);
    SET @Result = REPLACE(@Result, 'N', 5);
    SET @Result = REPLACE(@Result, 'P', 7);
    SET @Result = REPLACE(@Result, 'R', 9);
    SET @Result = REPLACE(@Result, 'S', 2);
    SET @Result = REPLACE(@Result, 'T', 3);
    SET @Result = REPLACE(@Result, 'U', 4);
    SET @Result = REPLACE(@Result, 'V', 5);
    SET @Result = REPLACE(@Result, 'W', 6);
    SET @Result = REPLACE(@Result, 'X', 7);
    SET @Result = REPLACE(@Result, 'Y', 8);
    SET @Result = REPLACE(@Result, 'Z', 9);
 
    -- Return the result of the function
    RETURN @Result
 
END

Now, we can focus on the check digit validation. The general procedure is as follows:

  1. replace the alphabetic characters by their numeric equivalents using the EBCDIC function above
  2. Iterate over the numerical representation of the VIN and calculate the sum of all products of the numerical representation with the respective weight
  3. Perform the modulo function on the sum to get the remaining value when divided by 11
  4. Build the check digit from the remainer
  5. Verify the calculated check digit with the actual one from the VIN

These steps are implemented as follows:

    -- Check 4: VIN Check Digit
    -- Part 2: The check digit is valid and must be verified
    IF (@Result = 0)
    BEGIN
        -- Use EBCDIC method for replacing characters
        SET @VINMod = [dbo].[ufnEBCDIC](@VIN);
 
        -- Set the weights for each position
        SET @Weights = '8765432X098765432';
 
        SET @Pos = 1;
        SET @Value = 0;
 
        WHILE (@Pos <= 17)
        BEGIN
 
            SET @Value = @Value + CAST(SUBSTRING(@VINMod, @Pos, 1) AS INT) * CAST(IIF(SUBSTRING(@Weights, @Pos, 1) = 'X', 10, SUBSTRING(@Weights, @Pos, 1)) AS INT);
 
            SET @Pos = @Pos + 1;
        END;
 
        -- Perform MOD
        SET @Value = @Value % 11;
 
        -- Create check digit
        IF (@Value = 10)
            SET @CheckDigit = 'X';
        ELSE
            SET @CheckDigit = @Value;
 
        -- Verify check digit
        SET @Result = IIF(SUBSTRING(@VIN, 9, 1) = @CheckDigit, 0, 2)
    END;

Putting Everything Together

The complete function is provided below:

-- =============================================
-- Author:        Michael Olschimke
--                www.olschimke.eu
-- Create date: 02-AUG-2012
-- Description:    This function checks the validity of the given
--              Vehicle Identification Number (VIN) and returns
--               0: if the VIN is correct
--                 1: if it is not possible to verify the check digit,
--                    but everything else looks good
--               2: if the check digit is invalid
--                    not necessarily an error!
--              -1: if the length is invalid
--                -2: if the format is invalid
--              -3: if the WMI (manufacturer) is unknown
-- =============================================
ALTER FUNCTION [dbo].[ufnCheckVIN] 
(
    @VIN nvarchar(17)
)
RETURNS int
AS
BEGIN
    -- The return variable
    DECLARE @Result int;
 
    -- Variables for Check 4
    DECLARE @MatchResult bit;
    DECLARE PatternCursor CURSOR FOR
    SELECT [dbo].[RegexMatch](@VIN, Pattern, 1) FROM IgnorePatterns;
 
    DECLARE @VINMod nvarchar(17);
    DECLARE @Weights nvarchar(17);
    DECLARE @Pos int;
    DECLARE @Value int;
 
    DECLARE @CheckDigit nvarchar(1);
 
    -- this function is based on the blog posting
    -- http://www.olschimke.eu/2012/08/02/dealing-with-vehicle-identification-numbers-vin-data-quality/
 
    -- uppercase VIN
    SET @VIN = UPPER(@VIN);
 
    -- Check 1: String Length
    SET @Result = IIF(LEN(LTRIM(RTRIM(@VIN))) = 17, 0, -1);
 
    -- Check 2: VIN Format using Regular Expression
    IF (@Result = 0)
        SET @Result = IIF([dbo].[RegexMatch](@VIN, 
            '^(([a-h,A-H,j-n,J-N,p-z,P-Z,0-9]{9})([a-h,A-H,j-n,J-N,p,P,r-t,R-T,v-z,V-Z,0-9])([a-h,A-H,j-n,J-N,p-z,P-Z,0-9])(d{6}))$', 1) = 1, 0, -2);
 
    -- Check 3: World Manufacturer Identifier
    IF (@Result = 0)
        SET @Result = IIF(EXISTS (SELECT RecID FROM Manufacturers 
            WHERE WMI = SUBSTRING(@VIN, 1, 3)), 0, -3);
 
    -- Check 4: VIN Check Digit
    -- Part 1: Check if the check digit is valid and if there are any exceptions defined in [IgnorePatterns]
    IF (@Result = 0)
    BEGIN
        OPEN PatternCursor;
        FETCH NEXT FROM PatternCursor INTO @MatchResult;
 
        IF (@MatchResult = 0)
        BEGIN
            WHILE @@FETCH_STATUS = 0
            BEGIN
                FETCH NEXT FROM PatternCursor INTO @MatchResult;
                IF (@MatchResult = 1)
                BEGIN
                    SET @Result = 1;
                    BREAK;
                END
            END;
        END
        ELSE
            SET @Result = 1;
 
        CLOSE PatternCursor;
        DEALLOCATE PatternCursor;
    END;
 
    -- Check 4: VIN Check Digit
    -- Part 2: The check digit is valid and must be verified
    IF (@Result = 0)
    BEGIN
        -- Use EBCDIC method for replacing characters
        SET @VINMod = [dbo].[ufnEBCDIC](@VIN);
 
        -- Set the weights for each position
        SET @Weights = '8765432X098765432';
 
        SET @Pos = 1;
        SET @Value = 0;
 
        WHILE (@Pos <= 17)
        BEGIN
 
            SET @Value = @Value + CAST(SUBSTRING(@VINMod, @Pos, 1) AS INT) * CAST(IIF(SUBSTRING(@Weights, @Pos, 1) = 'X', 10, SUBSTRING(@Weights, @Pos, 1)) AS INT);
 
            SET @Pos = @Pos + 1;
        END;
 
        -- Perform MOD
        SET @Value = @Value % 11;
 
        -- Create check digit
        IF (@Value = 10)
            SET @CheckDigit = 'X';
        ELSE
            SET @CheckDigit = @Value;
 
        -- Verify check digit
        SET @Result = IIF(SUBSTRING(@VIN, 9, 1) = @CheckDigit, 0, 2)
    END;
 
    -- Return the result of the function
    RETURN @Result;
END

Testing the Function

 To test the function, we can perform various selects on it as shown below:

-- Valid VIN
SELECT [dbo].[ufnCheckVIN] ('1M8GDM9AXKP042788')
GO
 
-- Not possible to verify the check digit
-- because this is a Lamborghini of 2001
SELECT [dbo].[ufnCheckVIN] ('ZHWGDM9111P042788')
GO
 
-- Invalid check digit
SELECT [dbo].[ufnCheckVIN] ('1M8GDM9A5KP042788')
GO
 
-- Invalid length
SELECT [dbo].[ufnCheckVIN] ('1M8GDM9A5KP04278')
GO
 
-- Invalid format
SELECT [dbo].[ufnCheckVIN] ('1M8GDM9A5KP04278A')
GO
 
-- Unknown WMI
SELECT [dbo].[ufnCheckVIN] ('XYZGDM9A5KP042788')
GO

Download

You can download the VIN Validator here.

As always: if you like the article (and download), please leave a comment.

9 thoughts on “Vehicle Identification Number (VIN) Validator for #SQLServer”

    • Hi Gaetan,

      the problem seems to be the regular expression in check #2. You can turn it off if it causes trouble.

      I need to further investigate this issue.

      Thanks for letting me know.

      Michael

      Reply ↓

    • Hi Gaetan,

      you need to replace the regular expression by this one:

      ^(([a-h,A-H,j-n,J-N,p-z,P-Z,0-9]{9})([a-h,A-H,j-n,J-N,p,P,r-t,R-T,v-z,V-Z,0-9])([a-h,A-H,j-n,J-N,p-z,P-Z,0-9]{7}))$

      The reason is that Ford is using sequential numbers which are 5 digits long, but my old expression expected them to be 6 digits long.

      I have changed that so that the sequential number can have any length.

      Thanks again for letting me know.

      Michael

      Reply ↓

Leave a Reply to Michael Olschimke Cancel reply

Your email address will not be published. Required fields are marked *