问验证GST标识号(GSTIN)

EN

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;

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

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

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

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

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

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

DECLARE @pan VARCHAR(10)
DECLARE @gst VARCHAR(15)
DECLARE @pan_in_gst VARCHAR(10)
DECLARE @v VARCHAR(5)


SET @pan = 'CABPG8363J'
SET @gst = '22CABPG8363J1Z1'
SET @pan_in_gst = 'CABPG8363A'

SET @v = CASE WHEN @pan = @pan_in_gst
THEN  'Yes'
ELSE 'No'
END

SELECT @pan AS 'Pan' , @gst AS 'GST', @pan_in_gst AS 'Pan IN GST', @v AS 'validation'