CREATE TABLE t
(
id VARCHAR(20),
num VARCHAR(100)
)
INSERT INTO t
SELECT '1','123123231231231131231233212312332311231123'
GO
CREATE FUNCTION F_TOTOLLIAN(
@s VARCHAR(200), --字符
@s1 VARCHAR(2) --开始字符
)
RETURNS INT
AS
BEGIN
DECLARE @start VARCHAR(2), @end VARCHAR(2),@len INT, @ifLian bit
IF @s1='' --如果开始字符不设置，那么以第一个字符开始算起走
BEGIN
SELECT @ifLian = 0, --是否连续
@start = LEFT(@s,1), --开始字符
@len = 0 --统计连续次数
WHILE @s > ''
BEGIN
SET @s = STUFF(@s,1,1,'')
SET @end = LEFT(@s,1)
IF CAST(@start AS INT ) + 1 = CAST(@end AS INT ) OR CAST(@start AS INT)/2 = CAST(@end AS INT)
SET @ifLian = 1
ELSE
IF @ifLian = 1
BEGIN
SET @ifLian = 0
SET @len = @len + 1
END
SET @start = @end
END
END
ELSE
BEGIN
SELECT @ifLian = 0, --是否连续
@start = @s1, --开始字符
@len = 0 --统计连续次数
WHILE CHARINDEX(@s1,@s) > 0
BEGIN
IF @ifLian = 0 --如果不是连续的
BEGIN
SET @s = STUFF(@s,1,CHARINDEX(@s1,@s),'') --那么就要寻找最近的开始字符的位置把字符串切取
SET @start = @s1
END
ELSE --如果是连续，那么取消前一个字符
SET @s = STUFF(@s,1,1,'')
SET @end = LEFT(@s,1)
--如果是连续字符
IF CAST(@start AS INT ) + 1 = CAST(@end AS INT ) OR CAST(@start AS INT)/2 = CAST(@end AS INT)
SET @ifLian = 1
ELSE
IF @ifLian = 1
BEGIN
SET @ifLian = 0
SET @len = @len + 1
END
SET @start = @end
END
SELECT @len = CASE WHEN @len = 0 THEN @len ELSE @len + 1 END
END
RETURN @len
END
GO
--如果没有设置起始的字符
SELECT *,TOTAL=DBO.F_TOTOLLIAN(num,'') FROM t
--如果设置起始的字符为'1'
SELECT *,TOTAL=DBO.F_TOTOLLIAN(num,'1') FROM t
DROP FUNCTION F_TOTOLLIAN
DROP TABLE t
id num TOTAL
-------------------- ---------------------------------------------------------------------------------------------------- -----------
1 123123231231231131231233212312332311231123 7
（所影响的行数为 1 行）
id num TOTAL
-------------------- ---------------------------------------------------------------------------------------------------- -----------
1 123123231231231131231233212312332311231123 6
（所影响的行数为 1 行）
