declare @s varchar(100) set @s='6735589402355366852487494330819'select COL='0' ,[count]=(len(@s+'a') - len(replace(@s,'0','')))%LEN(@s+'a') union select COL='1' ,[count]=(len(@s+'a') - len(replace(@s,'1','')))%LEN(@s+'a') union select COL='2' ,[count]=(len(@s+'a') - len(replace(@s,'2','')))%LEN(@s+'a') union select COL='3' ,[count]=(len(@s+'a') - len(replace(@s,'3','')))%LEN(@s+'a') union select COL='4' ,[count]=(len(@s+'a') - len(replace(@s,'4','')))%LEN(@s+'a') union select COL='5' ,[count]=(len(@s+'a') - len(replace(@s,'5','')))%LEN(@s+'a') union select COL='6' ,[count]=(len(@s+'a') - len(replace(@s,'6','')))%LEN(@s+'a') union select COL='7' ,[count]=(len(@s+'a') - len(replace(@s,'7','')))%LEN(@s+'a') union select COL='8' ,[count]=(len(@s+'a') - len(replace(@s,'8','')))%LEN(@s+'a') union select COL='9' ,[count]=(len(@s+'a') - len(replace(@s,'9','')))%LEN(@s+'a') /* COL count ---- ----------- 0 3 1 2 2 3 3 6 4 5 5 6 6 4 7 3 8 5 9 4*/
CREATE FUNCTION SPLIT_NUM(@S VARCHAR(500)) RETURNS @TB TABLE(ID INT) AS BEGIN DECLARE @NUM INT SET @NUM=1 WHILE @NUM<LEN(@S) BEGIN INSERT @TB SELECT SUBSTRING(@S,@NUM,1) SET @NUM=@NUM+1 END RETURN ENDDECLARE @S VARCHAR(500) SET @S='6735589402355366852487494330819'SELECT ID,COUNT(ID)NUM FROM DBO.SPLIT_NUM(@S) GROUP BY ID ID NUM ----------- ----------- 0 2 1 1 2 2 3 5 4 4 5 5 6 3 7 2 8 4 9 2(所影响的行数为 10 行)
set @s ='6735589402355366852487494330819'比如查 0select len(@s) - len(replace(@s,'0',''))
SET @S='6735589402355366852487494330819'SELECT '0' AS LEN(@S)-REPLACE(@S,'0','')
SET @S='6735589402355366852487494330819'SELECT '0' AS LEN(@S)-LEN(REPLACE(@S,'0',''))
0:2
1:X
2:X
3:X
4:X
5:X
6:X
7:X
8:X
9:X
的格式?
set @s='6735589402355366852487494330819'select COL='0' ,[count]=(len(@s+'a') - len(replace(@s,'0','')))%LEN(@s+'a')
union
select COL='1' ,[count]=(len(@s+'a') - len(replace(@s,'1','')))%LEN(@s+'a')
union
select COL='2' ,[count]=(len(@s+'a') - len(replace(@s,'2','')))%LEN(@s+'a')
union
select COL='3' ,[count]=(len(@s+'a') - len(replace(@s,'3','')))%LEN(@s+'a')
union
select COL='4' ,[count]=(len(@s+'a') - len(replace(@s,'4','')))%LEN(@s+'a')
union
select COL='5' ,[count]=(len(@s+'a') - len(replace(@s,'5','')))%LEN(@s+'a')
union
select COL='6' ,[count]=(len(@s+'a') - len(replace(@s,'6','')))%LEN(@s+'a')
union
select COL='7' ,[count]=(len(@s+'a') - len(replace(@s,'7','')))%LEN(@s+'a')
union
select COL='8' ,[count]=(len(@s+'a') - len(replace(@s,'8','')))%LEN(@s+'a')
union
select COL='9' ,[count]=(len(@s+'a') - len(replace(@s,'9','')))%LEN(@s+'a')
/*
COL count
---- -----------
0 3
1 2
2 3
3 6
4 5
5 6
6 4
7 3
8 5
9 4*/
CREATE FUNCTION SPLIT_NUM(@S VARCHAR(500))
RETURNS @TB TABLE(ID INT)
AS
BEGIN
DECLARE @NUM INT
SET @NUM=1
WHILE @NUM<LEN(@S)
BEGIN
INSERT @TB SELECT SUBSTRING(@S,@NUM,1)
SET @NUM=@NUM+1
END
RETURN
ENDDECLARE @S VARCHAR(500)
SET @S='6735589402355366852487494330819'SELECT ID,COUNT(ID)NUM FROM DBO.SPLIT_NUM(@S) GROUP BY ID
ID NUM
----------- -----------
0 2
1 1
2 2
3 5
4 4
5 5
6 3
7 2
8 4
9 2(所影响的行数为 10 行)