sqlserver 掩码判断

/*
品牌条码校验
2024-10-17
*/
CREATE PROCEDURE uspBrandMaskCheck
    @SN VARCHAR(50),
    @Brand VARCHAR(50),
	@Result			BIT OUTPUT,
    @ErrorMsg		NVARCHAR(500) OUTPUT
AS
BEGIN
SET @Result = 1

IF NOT EXISTS(
SELECT 1 FROM dbo.Prod_BrandMask WHERE Brand=@Brand
)
BEGIN
SET @Result = 0		
SET @ErrorMsg='品牌掩码未维护！'
RETURN;
END
DECLARE @Mask VARCHAR(50)
SELECT @Mask=Mask FROM dbo.Prod_BrandMask WHERE Brand=@Brand
--比对长度
IF LEN(@Mask)<>LEN(@SN)
BEGIN
SET @Result = 1		
SET @ErrorMsg='该电芯SN不符合该品牌电芯掩码规则！'
END

DECLARE @isMatch BIT = 1; -- 默认认为是匹配
DECLARE @len INT = LEN(@Mask);
DECLARE @i INT = 1;

WHILE @i <= @len
BEGIN
    DECLARE @maskChar CHAR(1) = SUBSTRING(@Mask, @i, 1);
    DECLARE @inputChar CHAR(1) = SUBSTRING(@SN, @i, 1);

    IF @maskChar = '@'
    BEGIN
        -- 检查是否是数字
        IF @inputChar NOT LIKE '[0-9]'
        BEGIN
            SET @isMatch = 0; -- 不匹配
            BREAK; -- 退出循环
        END
    END
    ELSE IF @maskChar = '#'
    BEGIN
        -- 检查是否是字母
        IF @inputChar NOT LIKE '[A-Za-z]'
        BEGIN
            SET @isMatch = 0; -- 不匹配
            BREAK; -- 退出循环
        END
    END
    ELSE IF @maskChar = '?' OR @maskChar = '？'
    BEGIN
        -- 检查是否是字母或数字
        IF @inputChar NOT LIKE '[0-9A-Za-z]'
        BEGIN
            SET @isMatch = 0; -- 不匹配
            BREAK; -- 退出循环
        END
    END
    ELSE IF @maskChar <> @inputChar
    BEGIN
        -- 检查固定部分是否相同
        SET @isMatch = 0; -- 不匹配
        BREAK; -- 退出循环
    END

    SET @i = @i + 1; -- 移动到下一个字符
END

IF @isMatch = 0
BEGIN
SET @Result = 1		
SET @ErrorMsg='该电芯SN不符合该品牌电芯掩码规则！'
END
END;