34,590
社区成员
发帖
与我相关
我的任务
分享
GO
create function [dbo].[m_split5](@c varchar(2000),@split varchar(2))
returns @d table(col varchar(200),icount int)
as
begin
while(charindex(@split,@c)<>0)
begin
insert @d(col) values (substring(@c,1,charindex(@split,@c)-1))
set @c = stuff(@c,1,charindex(@split,@c),'')
end
insert @d(col) values (@c)
update @d set icount= (select count(*) from @d b where col=a.col) from @d a
return
end
select distinct * from dbo.m_split5(replace('HOW,OLD,ARE;YOU;HOW,DO,YOU,DO',',',';'),';')
create function [fnwordinstrcount](@str varchar(max),@separator varchar(3))
returns @wordinstrcount table
(
sword varchar(50),
icount int
)
with encryption
as
begin
declare @iindex int
declare @sword varchar(50)
set @str = @str + @separator
set @iindex = charindex(@separator,@str)
while @iindex > 0
begin
set @sword = left(@str,@iindex - 1)
if exists (select 1 from @wordinstrcount where sword = @sword)
update @wordinstrcount set icount = icount + 1 where sword = @sword
else
insert into @wordinstrcount select @sword,1
set @str = right(@str,len(@str)-@iindex)
set @iindex = charindex(@separator,@str)
end
return
end
go
select * from [dbo].[fnwordinstrcount]('how,old,are,you,how,do,you,do',',')
drop function [fnwordinstrcount]
/*******************
sword icount
-------------------------------------------------- -----------
how 2
old 1
are 1
you 2
do 2
(5 行受影响)
/*按照符号分割字符串*/
create function [dbo].[m_split3](@c varchar(2000),@split varchar(2))
returns @d table(col varchar(200),icount int)
as
begin
declare @t table(col varchar(200))
while(charindex(@split,@c)<>0)
begin
insert @t(col) values (substring(@c,1,charindex(@split,@c)-1))
set @c = stuff(@c,1,charindex(@split,@c),'')
-- SET @c = substring(@c,charindex(' ',@c)+1,len(@c))
end
insert @t(col) values (@c)
insert @d select col,count(1) from @t group by col order by 2 desc
return
end
select * from dbo.m_split3(replace('HOW,OLD,ARE;YOU;HOW,DO,YOU,DO',',',';'),';')
/*
col icount
--- ----
DO 2
HOW 2
YOU 2
ARE 1
OLD 1
*/
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[fnWordInStrCount]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[fnWordInStrCount]
GO
CREATE FUNCTION [fnWordInStrCount](@Str VARCHAR(MAX),@Separator VARCHAR(3))
RETURNS @WordInStrCount TABLE
(
sWord VARCHAR(50)
,iCount INT
)
WITH ENCRYPTION
AS
BEGIN
DECLARE @Len INT,@iIndex INT,@iCount INT
DECLARE @sWord VARCHAR(50)
SET @iCount = 0
SET @Len = LEN(@Str)
WHILE @Len > 0
BEGIN
----分隔符在前面就去掉
WHILE (left(@Str,len(@Separator))=@Separator)
begin
set @Str=right(@Str,len(@str)-len(@Separator))
end
--取出第一个单词 并且算出该单词的个数
SET @sWord = LEFT(@Str,CHARINDEX(@Separator,@Str)-1)
select @iCount=(len(@Str)-len(replace(@Str,@sWord,'')))/len(@sWord)
--插入该单词记录
INSERT INTO @WordInStrCount(sWord,iCount)
VALUES(@sWord,@iCount)
---重建@Str
SET @Str = REPLACE(@Str,@sWord,'')
SET @Len = LEN(@Str)
if @Str is null
set @Len=0
END
RETURN
END
GO
SELECT * FROM [dbo].[fnWordInStrCount]('HOW,;OLD,;ARE,;YOU,;HOW,;DO,;YOU,;DO',',;')
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[fnWordInStrCount]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[fnWordInStrCount]
GO
CREATE FUNCTION [fnWordInStrCount](@Str VARCHAR(MAX),@Separator VARCHAR(3))
RETURNS @WordInStrCount TABLE
(
sWord VARCHAR(50)
,iCount INT
)
WITH ENCRYPTION
AS
BEGIN
DECLARE @Len INT,@iIndex INT,@iCount INT
DECLARE @sWord VARCHAR(50)
SET @iCount = 0
SET @Len = LEN(@Str)
SET @iIndex = 1
WHILE @Len > 0
BEGIN
----分隔符在前面就去掉
WHILE (left(@Str,len(@Separator))=@Separator)
begin
set @Str=right(@Str,len(@str)-len(@Separator))
end
--取出第一个单词 并且算出该单词的个数
SET @sWord = LEFT(@Str,CHARINDEX(@Separator,@Str,1)-LEN(@Separator))
select @iCount=(len(@Str)-len(replace(@Str,@sWord,'')))/len(@sWord)
--插入该单词记录
INSERT INTO @WordInStrCount(sWord,iCount)
VALUES(@sWord,@iCount)
---重建@Str
SET @Str = REPLACE(@Str,@sWord,'')
SET @Len = LEN(@Str)
if @Str is null
set @Len=0
END
RETURN
END
GO
SELECT * FROM [dbo].[fnWordInStrCount]('HOW,OLD,ARE,YOU,HOW,DO,YOU,DO',',')
declare @str varchar(20)
declare @sql varchar(200)
set @sql='1234a7897a'
set @str='a'
select (len(@sql)-len(replace(@sql,@str,'')))/len(@str)
select (len(@sql)-len(replace(@sql,@str,''))) /len(@str) as 个数
declare @str varchar(20)
declare @sql varchar(20)
select len(@sql)-len(replace(@sql,@str,''))/len(@str) as 个数
/*按照符号分割字符串*/
create function [dbo].[m_split](@c varchar(2000),@split varchar(2))
returns @t table(col varchar(200))
as
begin
while(charindex(@split,@c)<>0)
begin
insert @t(col) values (substring(@c,1,charindex(@split,@c)-1))
set @c = stuff(@c,1,charindex(@split,@c),'')
-- SET @c = substring(@c,charindex(' ',@c)+1,len(@c))
end
insert @t(col) values (@c)
return
end
select col,icount=count(1)
from dbo.m_split('HOW,OLD,ARE,YOU,HOW,DO,YOU,DO',',')
group by col order by 2 desc
/*
col icount
--- ----
DO 2
HOW 2
YOU 2
ARE 1
OLD 1
*/