declare @str as varchar(100) set @str = '1231232312312313123'select (len(@str)-len(replace(@str,'1231','')))/4
CREATE TABLE t ( id VARCHAR(20), num VARCHAR(100) )INSERT INTO t SELECT '1','12312323123123131231233212312312313112312312312312312312312323' GO --方法一字符读取方法 CREATE FUNCTION F_TOTAL(@s VARCHAR(200), @s1 VARCHAR(20)) RETURNS INT AS BEGIN DECLARE @re INT, --返回结果 @st INT, --开始数字 @e INT, --结束数字 @i INT, --本次统计结果 @len INT --要统计的字符长度 SELECT @re = 0,@st = CHARINDEX(@s1,@s), @e = @st+1,@i = 0,@len = LEN(@s1) WHILE CHARINDEX(@s1,@s,@e)>0 BEGIN SET @e = CHARINDEX(@s1,@s,@st+1) IF @e - @st = @len SET @i = @i + 1 ELSE IF @e - @st > @len --如果结束数字和开始数字之和大于统计字符长度,表示统计的字符已经不再相联了 BEGIN --如果统计的连续次数大于结果,那么把该次统计存储到结果 IF @re < @i SET @re = @i SET @i = 0 END ELSE --表示结束 BEGIN IF @re < @i SET @re = @i BREAK END SET @st = @e END RETURN CASE WHEN @re <> 0 THEN @re + 1 ELSE @re END --因为统计始终少了一次,所以必须加一 END GO--方法二,字符替换方法 CREATE FUNCTION F_TOTALONE(@s VARCHAR(200), @s1 VARCHAR(20)) RETURNS INT AS BEGIN DECLARE @len INT --得到总共@s1出现的次数 SET @len = (LEN(@s)-LEN(REPLACE(@s,@s1,'')))/LEN(@s1) --将@s1替换成'@' SET @s = REPLACE(@s,@s1,'@') WHILE @len >= 0 BEGIN --如果符合条件,那么就退出 IF CHARINDEX(REPLICATE('@',@len),@s)>0 BREAK SET @len = @len - 1 END RETURN @len END GOSELECT *,TOTALNUM=DBO.F_TOTAL(num,'123') FROM t SELECT *,TOTALNUM=DBO.F_TOTALONE(num,'123') FROM tDROP FUNCTION F_TOTAL DROP FUNCTION F_TOTALONE DROP TABLE tid num TOTALNUM -------------------- ---------------------------------------------------------------------------------------------------- ----------- 1 12312323123123131231233212312312313112312312312312312312312323 8(所影响的行数为 1 行)id num TOTALNUM -------------------- ---------------------------------------------------------------------------------------------------- ----------- 1 12312323123123131231233212312312313112312312312312312312312323 8(所影响的行数为 1 行)
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 行)
多谢各位就是 wgzaaa 那个意思, 哈哈,
wgzaaa 这句不怎么懂,解释下啊:update #b set b=@b,@b=(case when a-@a in (1,-2) then @b+1 else 1 end),@a=a
set @str = '1231232312312313123'select '12的个数'=(len(@str) - len(replace(@str,'12','')))/2 ,
'23的个数'=(len(@str) - len(replace(@str,'23','')))/2 ,
'31的个数'=(len(@str) - len(replace(@str,'31','')))/2 ,
'总数' = (len(@str) - len(replace(@str,'12','')))/2 +
(len(@str) - len(replace(@str,'23','')))/2 +
(len(@str) - len(replace(@str,'31','')))/2/*
12的个数 23的个数 31的个数 总数
----------- ----------- ----------- -----------
5 6 5 16(所影响的行数为 1 行)
*/
declare @str as varchar(100)
set @str = '1231232312312313123'select (len(@str)-len(replace(@str,'1231','')))/4
(
id VARCHAR(20),
num VARCHAR(100)
)INSERT INTO t
SELECT '1','12312323123123131231233212312312313112312312312312312312312323'
GO
--方法一字符读取方法
CREATE FUNCTION F_TOTAL(@s VARCHAR(200), @s1 VARCHAR(20))
RETURNS INT
AS
BEGIN
DECLARE @re INT, --返回结果
@st INT, --开始数字
@e INT, --结束数字
@i INT, --本次统计结果
@len INT --要统计的字符长度
SELECT @re = 0,@st = CHARINDEX(@s1,@s), @e = @st+1,@i = 0,@len = LEN(@s1)
WHILE CHARINDEX(@s1,@s,@e)>0
BEGIN
SET @e = CHARINDEX(@s1,@s,@st+1)
IF @e - @st = @len
SET @i = @i + 1
ELSE IF @e - @st > @len --如果结束数字和开始数字之和大于统计字符长度,表示统计的字符已经不再相联了
BEGIN
--如果统计的连续次数大于结果,那么把该次统计存储到结果
IF @re < @i
SET @re = @i
SET @i = 0
END
ELSE --表示结束
BEGIN
IF @re < @i
SET @re = @i
BREAK
END
SET @st = @e
END
RETURN CASE WHEN @re <> 0 THEN @re + 1 ELSE @re END --因为统计始终少了一次,所以必须加一
END
GO--方法二,字符替换方法
CREATE FUNCTION F_TOTALONE(@s VARCHAR(200), @s1 VARCHAR(20))
RETURNS INT
AS
BEGIN
DECLARE @len INT
--得到总共@s1出现的次数
SET @len = (LEN(@s)-LEN(REPLACE(@s,@s1,'')))/LEN(@s1)
--将@s1替换成'@'
SET @s = REPLACE(@s,@s1,'@') WHILE @len >= 0
BEGIN
--如果符合条件,那么就退出
IF CHARINDEX(REPLICATE('@',@len),@s)>0
BREAK
SET @len = @len - 1
END
RETURN @len
END
GOSELECT *,TOTALNUM=DBO.F_TOTAL(num,'123') FROM t
SELECT *,TOTALNUM=DBO.F_TOTALONE(num,'123') FROM tDROP FUNCTION F_TOTAL
DROP FUNCTION F_TOTALONE
DROP TABLE tid num TOTALNUM
-------------------- ---------------------------------------------------------------------------------------------------- -----------
1 12312323123123131231233212312312313112312312312312312312312323 8(所影响的行数为 1 行)id num TOTALNUM
-------------------- ---------------------------------------------------------------------------------------------------- -----------
1 12312323123123131231233212312312313112312312312312312312312323 8(所影响的行数为 1 行)
insert #a select 1 insert #a select 2 insert #a select 3
insert #a select 1 insert #a select 2 insert #a select 3
insert #a select 2 insert #a select 3
insert #a select 1 insert #a select 2 insert #a select 3
insert #a select 1 insert #a select 2 insert #a select 3
insert #a select 1 insert #a select 3
insert #a select 1 insert #a select 2 insert #a select 3
-----------------------------------------
SELECT a,b=0 into #B FROM #A
declare @a int,@b int
select @b=0,@a=0
update #b set b=@b,@b=(case when a-@a in (1,-2) then @b+1 else 1 end),@a=a
select max(b)最多连续出现的次数 from #b
drop table #b
-------------------
最多连续出现的次数
-----------
9(所影响的行数为 1 行)
(
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 行)
这句不怎么懂,解释下啊:update #b set b=@b,@b=(case when a-@a in (1,-2) then @b+1 else 1 end),@a=a