问如何逐个处理SQL字符串来构建匹配权重？

EN

CREATE FUNCTION CalculateMatchWeight 
(
    -- Add the parameters for the function here
    @MatchCode varchar(100)
)
RETURNS smallint
AS
BEGIN
    -- Declare the return variable here
    DECLARE @Result smallint = 0;

    -- Add the T-SQL statements to compute the return value here
    DECLARE @Pos int = 1, @N0 int = ascii('0'), @N9 int = ascii('9'), @AA int = ascii('A'), @AZ int = ascii('Z'), @Wild int = ascii('%'), @Range int = ascii('[');
    DECLARE @Asc int;
    DECLARE @WorkingString varchar(100) = upper(@MatchCode)

    WHILE @Pos <= LEN(@WorkingString)
    BEGIN
        SET @Asc = ascii(substring(@WorkingString, @Pos, 1));

        If ((@Asc between @N0 and @N9) or (@Asc between @AA and @AZ))
            SET @Result = @Result + 2;

        ELSE
        BEGIN
            -- Check wildcard matching, update value according to match strength, and stop calculating further.
            -- TODO: In the future we may wish to have match codes with wildcards not just at the end; try to figure out a mechanism to calculating that case.
            IF (@Asc = @Range)
            BEGIN
                SET @Result = @Result + 2;
                SET @Pos = 100;
            END
            IF (@Asc = @Wild)
            BEGIN
                SET @Result = @Result + 1;
                SET @Pos = 100;
            END
        END

        SET @Pos = @Pos + 1
    END

    -- Return the result of the function
    RETURN @Result
END

SELECT [dbo].[CalculateMatchWeight] ('12345');      -- Most precise (10)
SELECT [dbo].[CalculateMatchWeight] ('123[4-5]');   -- Middle       (8)
SELECT [dbo].[CalculateMatchWeight] ('123%');       -- Least        (7)

CREATE TRIGGER TRG_ENTRY_FORMAT_CalcMatchWeight
   ON  ENTRY_FORMAT
   AFTER INSERT,UPDATE
AS 
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for trigger here
    DECLARE @NewMatchWeight smallint = (select dbo.CalculateMatchWeight(inserted.MATCH_CODE) from inserted),
            @CurrentMatchWeight smallint = (select inserted.MATCH_WEIGHT from inserted);

    IF (@CurrentMatchWeight <> @NewMatchWeight)
    BEGIN
        UPDATE ENTRY_FORMAT
           SET MATCH_WEIGHT = @NewMatchWeight
          FROM inserted
         WHERE ENTRY_FORMAT.[MATCH_CODE] = inserted.[MATCH_CODE]
           AND ENTRY_FORMAT.[SEQUENCE] = inserted.[SEQUENCE]
    END
END