SQL Server stored procedure EAN code validation

Question

I need to write a stored procedure or funtion to validate the insert of EAN13 code in SQL Server.

Can anyone help me?

What you tried so far? Please read http://stackoverflow.com/help/how-to-ask before posting questions — jean, Mar 20 '15 at 16:56
What do you mean by *inclusion*? And also: validate ***how*** ? Lookup against a set of valid values? Not quite clear.... — marc_s, Mar 20 '15 at 16:56

score 0 · Accepted Answer · answered Mar 20 '15 at 17:22

Resolved,

I created a function for validation:

CREATE FUNCTION [sp_ean](@Ean varchar(max)) RETURNS INT AS
BEGIN
    DECLARE @Factor INT
    DECLARE @Sum INT
    DECLARE @Len INT
    DECLARE @CC INT
    DECLARE @CA INT
    DECLARE @Result NVARCHAR(MAX)

    SET @Len = LEN(@Ean)
    SET @Sum = 0
    SET @Factor = 3

    IF @Len = 14 OR @Len = 13 OR @Len = 12 OR @Len = 8 
        BEGIN
            WHILE @Len > 0 
                BEGIN
                    IF @Len  13
                        BEGIN
                            SET @Sum = @Sum + SUBSTRING(@Ean,@Len,1) * @Factor
                            SET @Factor = 4 - @Factor
                        END
                    SET @Len = @Len - 1
                END

            SET @CC = ((1000 - @Sum) % 10)
            SET @CA = SUBSTRING(@Ean,LEN(@Ean),1)

            IF  @CC = @CA
                BEGIN
                    SET @Result = 0
                END
            ELSE
                BEGIN
                    SET @Result = 1
                END
        END
    ELSE
        BEGIN
            SET @Result = 1
        END
    RETURN (@Result)
    END

Result: 0 to true or 1 to false

Thanks!

You may want to mention that EAN13 is a very common code used to identify products at checkout counters. — benjamin moskovits, Mar 20 '15 at 17:28

score 0 · Answer 2 · answered Mar 20 '15 at 17:49

Perhaps you'll find something like this to be cleaner. It handles the variable length input and also has the advantage of being usable as a check constraint as pointed out by Joe Celko.

CASE WHEN CAST(SUBSTRING(REVERSE(ean), 1, 1) AS INTEGER) =
        10 - (
               3 * CAST(SUBSTRING(REVERSE(ean) + '000000', 2, 1)) AS INT)
               1 * CAST(SUBSTRING(REVERSE(ean) + '000000', 3, 1)) AS INT)
               3 * CAST(SUBSTRING(REVERSE(ean) + '000000', 4, 1)) AS INT)
               1 * CAST(SUBSTRING(REVERSE(ean) + '000000', 5, 1)) AS INT)
               3 * CAST(SUBSTRING(REVERSE(ean) + '000000', 6, 1)) AS INT)
               1 * CAST(SUBSTRING(REVERSE(ean) + '000000', 7, 1)) AS INT)
               3 * CAST(SUBSTRING(REVERSE(ean) + '000000', 8, 1)) AS INT)
               1 * CAST(SUBSTRING(REVERSE(ean) + '000000', 9, 1)) AS INT)
               3 * CAST(SUBSTRING(REVERSE(ean) + '000000', 10, 1)) AS INT)
               1 * CAST(SUBSTRING(REVERSE(ean) + '000000', 11, 1)) AS INT)
               3 * CAST(SUBSTRING(REVERSE(ean) + '000000', 12, 1)) AS INT)
               1 * CAST(SUBSTRING(REVERSE(ean) + '000000', 13, 1)) AS INT)
               3 * CAST(SUBSTRING(REVERSE(ean) + '000000', 14, 1)) AS INT)
             ) % 10
THEN 1 ELSE 0 END

SQL Server stored procedure EAN code validation

2 Answers2