1

The following image describes the format of the GST Identification Number:

enter image description here

  • The first 2 digits denote the unique State Code in accordance with the Indian Census 2011. For instance, the State Code of New Delhi is '07' and that of Karnataka is '29'.

  • The next 10 characters denote the PAN (Permanent Account Number) of the taxpayer.

  • The 13th digit denotes the Registration Number (or Entity Number) of the tax payer with the same PAN.

  • The 14th digit is 'Z' by default for all – not intending anything currently.

  • The 15th digit is the Check sum digit – can be a number or an alphabetical character.

Perhaps I can carry out the validation by means of a PATINDEX or a Regex?

2
  • 1
    No doubts,this is good programming question.if possible you should validate GST no while insert/update in front end like c#.Front end is very suitable for this type of job.Therefore no need to validate while fetch. – KumarHarsh Apr 12 '18 at 4:35
  • 2
    @KumarHarsh - validating at the database level is far more reliable than doing it in the front-end. Check constraints are designed for this type of validation, and prevent any invalid data being inserted, whether the front-end inserts it, or something else such as a bulk-load. – Hannah Vernon Apr 12 '18 at 19:52
4

You don't need PATINDEX or RegEx for this.

CREATE TABLE #floob
(
  GSTINColumn char(15),
  CONSTRAINT CheckGSTINColumn CHECK 
  (
    GSTINColumn  LIKE '[0-9][0-9][0-9A-Z]'
      + '[0-9A-Z][0-9A-Z][0-9A-Z][0-9A-Z]'
      + '[0-9A-Z][0-9A-Z][0-9A-Z][0-9A-Z]'
      + '[0-9A-Z][0-9]Z[0-9A-Z]'
  )
);

-- succeeds
INSERT #floob(GSTINColumn) VALUES('22AAAAA0000A1Z5');
GO
-- succeeds
INSERT #floob(GSTINColumn) VALUES('675T3E5600AZ7Z9');
GO
-- fails
INSERT #floob(GSTINColumn) VALUES('22AAAAA0000A1X5');
GO

DROP TABLE #floob;
1
  • Thanks a Lot this is worked for me. I used in select statement as i need like below Select CASE WHEN 'GSTIN_CODE' LIKE '[0-9][0-9][0-9A-Z]' + '[0-9A-Z][0-9A-Z][0-9A-Z][0-9A-Z]' + '[0-9A-Z][0-9A-Z][0-9A-Z][0-9A-Z]' + '[0-9A-Z][0-9]Z[0-9A-Z]' THEN 'True' ELSE 'False' END – Sunil Patil Apr 12 '18 at 15:51
5

To supplement Aaron's completely valid answer, you might decide to do a more in-depth validation of the GSTIN number by validating the correctness of the check-digit, which appears as the last digit in the number.

The code below creates a number of functions that will be used by a CHECK CONSTRAINT to validate new rows, and any modifications to the GSTIN number.

The first two functions provide arbitrary mapping of the ASCII characters contained in the GSTIN number into base-36; that is 0 maps to 0, 9 maps to 9, A maps to 10 and Z maps to 35:

IF OBJECT_ID(N'dbo.map_char', N'FN') IS NOT NULL
DROP FUNCTION dbo.map_char;
GO
CREATE FUNCTION dbo.map_char(@c char(1))
RETURNS int
AS
BEGIN
    DECLARE @val int;
    SET @c = UPPER(@c);
    IF ASCII(@c) >= 48 AND ASCII(@c) <= 57
        SET @val = ASCII(@c) - 48;
    IF ASCII(@c) >= 65 AND ASCII(@c) <= 90
        SET @val = (ASCII(@c) - 65) + 10;
    RETURN @val;
END
GO
IF OBJECT_ID(N'dbo.unmap_char', N'FN') IS NOT NULL
DROP FUNCTION dbo.unmap_char;
GO
CREATE FUNCTION dbo.unmap_char(@v int)
RETURNS char(1)
AS
BEGIN
    DECLARE @c char(1);
    IF @v >= 0 AND @v <=9 
        SET @c = CHAR(@v + 48);
    IF @v >= 10 AND @v <= 90
        SET @c = CHAR((@v + 65) - 10);
    RETURN @c;
END
GO

I've been unable to determine for certain if the government of India uses this same encoding, however it seems to work for the values provided.

This code uses the above dbo.map_char function to validate the check-digit in the given GSTIN number.

CREATE FUNCTION dbo.fn_validate_gstin
(
    @inp char(15)
)
RETURNS tinyint
AS
BEGIN
    DECLARE @return tinyint;
    DECLARE @i int = LEN(@inp);
    DECLARE @factor int = 1;
    DECLARE @char char(1);
    DECLARE @codepoint int;
    DECLARE @addend int;
    DECLARE @sum int = 0;
    IF @i <> 15 /* GSTIN MUST be 15 characters to be valid */
    BEGIN
        SET @return = 0;
    END
    ELSE
    BEGIN
        WHILE @i > 0
        BEGIN
            SET @codepoint = dbo.map_char(SUBSTRING(@inp, @i, 1));
            SET @addend = @factor * @codepoint;
            SET @addend = (@addend / 36) + (@addend % 36);
            SET @sum += @addend;
            IF @factor = 2 SET @factor = 1 ELSE SET @factor = 2;
            SET @i -= 1;
        END
    END
    DECLARE @remainder int = @sum % 36;
    IF @remainder = 0 SET @return = 1 ELSE SET @return = 0;
    RETURN @return;
END
GO

Note, there is absolutely no error-checking in this code; I leave that as an exercise for the reader. The function returns 1 if the GSTIN contains a valid check-digit as the last digit. If the check-digit is invalid, it returns 0.

Here I create a table that implements the dbo.fn_validate_gstin function:

CREATE TABLE dbo.t
(
    i char(15) NOT NULL
        CONSTRAINT CK_t_valid_gstin
        CHECK (dbo.fn_validate_gstin(i) = 1)
);

Here we insert several "test" GSTIN numbers:

INSERT INTO dbo.t (i) VALUES ('123456789012345');
INSERT INTO dbo.t (i) VALUES ('27AAFFM5744C1ZE');
INSERT INTO dbo.t (i) VALUES ('27AAACE7932L1ZC');

The first insert fails. The second and third inserts succeed. Attempting to insert an invalid GSTIN numbers result in this error:

Msg 547, Level 16, State 0, Line 80
The INSERT statement conflicted with the CHECK constraint "CK_t_valid_gstin".
The conflict occurred in database "tempdb", table "dbo.t", column 'i'.

Note, the presence of a scalar function as part of a constraint prevents queries using parallelism. This may or may not be problematic for your system. If you need parallelism, you might consider using an INSTEAD OF trigger on the table to check the GSTIN numbers at insert or update. For example:

IF OBJECT_ID(N'dbo.t_with_trigger', N'U') IS NOT NULL
DROP TABLE dbo.t_with_trigger;
GO
CREATE TABLE dbo.t_with_trigger
(
    i char(15) NOT NULL
);

GO
CREATE TRIGGER t_validate
ON dbo.t_with_trigger
INSTEAD OF INSERT, UPDATE
AS
BEGIN
    SET NOCOUNT ON;
    BEGIN TRANSACTION
    DECLARE @bOk bit = 1;
    IF EXISTS (
        SELECT TOP(1) 1
        FROM inserted i
        WHERE dbo.fn_validate_gstin(i.i) = 0
        UNION ALL
        SELECT TOP(1) 1
        FROM deleted d
        WHERE dbo.fn_validate_gstin(d.i) = 0
        )
    BEGIN
        SET @bOk = 0; 
    END
    IF @bOk = 1 
    BEGIN
        DELETE
        FROM dbo.t_with_trigger
        FROM dbo.t_with_trigger t
            INNER JOIN deleted d ON t.i = d.i;
        INSERT INTO dbo.t_with_trigger(i)
        SELECT i.i
        FROM inserted i;
        COMMIT TRANSACTION;
    END
    ELSE
    BEGIN
        ROLLBACK TRANSACTION;
        DECLARE @msg varchar(1000);
        SET @msg = 'Attempted to insert/update using an invalid GSTIN number.';
        RAISERROR (@msg, 14, 1);
    END
END
GO

Inserting an invalid GSTIN number:

INSERT INTO dbo.t_with_trigger (i) 
VALUES ('123456789012345')
    , ('27AAFFM5744C1ZE')
    , ('27AAACE7932L1ZC');

results in this error:

Msg 50000, Level 14, State 1, Procedure t_validate, Line 37 [Batch Start Line 129]
Attempted to insert/update using an invalid GSTIN number.
Msg 3609, Level 16, State 1, Line 130
The transaction ended in the trigger. The batch has been aborted.

I based my implementation around the LUHN algorithm.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.