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(MAX)
)
    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,
                        CORRECT_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), @CORRECT_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
        IF (@VIN LIKE '%[^0-9A-Z]%')
            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

            IF @REMAINDER = 10
                SET @CORRECT_CHECK_DIGIT = 'X'
            ELSE
                SET @CORRECT_CHECK_DIGIT = CAST(@REMAINDER AS char)
        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, CORRECT_CHECK_DIGIT, PRODUCT, REMAINDER)
    VALUES (@VALID, @LENGTH_VALID, @FORMAT_VALID, @CHECKSUM_VALID, @CHECK_DIGIT, @CORRECT_CHECK_DIGIT, @PRODUCT, @REMAINDER)

    RETURN
END