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.
