sql server ---针对字符串@departmentCode，从右边算起，如果一定数量（如2

DROP FUNCTION computeExaminePurview
GO
/**
* 针对字符串@departmentCode，从右边算起，如果一定数量（如2个字符）的字符满足指定的字符，
* 则级别加1，最终返回级别的值
*/
CREATE FUNCTION computeExaminePurview
(
    @departmentCode varchar(100)
)
RETURNS int
AS
BEGIN
    DECLARE @purview int
    DECLARE @str varchar(100)
    DECLARE @splitCharCount int
    DECLARE @purviewChar varchar(100)

    SET @purview = 1;
    SET @splitCharCount = 2   
    SET @purviewChar = '00'
    SET @str = @departmentCode
    WHILE LEN(@str) > 0
    BEGIN
        --从右往左算，每@splitCharCount个字符如果与预定的@purviewChar相同，则级别加1
        IF RIGHT(@str,@splitCharCount) = @purviewChar
            SET @purview = @purview + 1
        ELSE
            --若有一个不满足条件，则返回，无需继续计算
            RETURN @purview
       
        --将@str用左边剩余的字符重新赋值
        IF LEN(@str) >= @splitCharCount
            SET @str = LEFT(@str,LEN(@str) - @splitCharCount)
        ELSE
            SET @str = ''
    END

    RETURN @purview
END

select dbo.computeExaminePurview('400000'),dbo.computeExaminePurview('440010'),dbo.computeExaminePurview('440200')
 -- column1     column2     column3   
 ----------  ----------  ----------
 -- 3           1           2