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
评论排行榜