在工作过程中突然间遇到一个要统计某条记录,某个字段中用逗号分开后的字符个数。
如某记录某段的内容如下:a,b,c,d,e,f ,g 统计的结果应该为: 7 。找了一下sql,并没有找到这个函数,
于是叫同事lucky帮我写了一个(因为我的sql实在太菜了,只会select)。
sql function:
CREATE
FUNCTION
[
dbo
]
.
[
f_count_sub_string
]
( @var varchar ( 500 ) ,
@split char ( 1 ) )
RETURNS int AS
BEGIN
declare @cur char ( 1 )
declare @i int
select @i = 0
declare @total int
select @total = len ( @var )
declare @num int
select @num = 1
while @i <= @total
begin
select @i = @i + 1
select @cur = substring ( @var , @i , 1 )
if @cur = @split
select @num = @num + 1
end
return @num
END
( @var varchar ( 500 ) ,
@split char ( 1 ) )
RETURNS int AS
BEGIN
declare @cur char ( 1 )
declare @i int
select @i = 0
declare @total int
select @total = len ( @var )
declare @num int
select @num = 1
while @i <= @total
begin
select @i = @i + 1
select @cur = substring ( @var , @i , 1 )
if @cur = @split
select @num = @num + 1
end
return @num
END
使用方法:
select
dbo.f_count_sub_string(
'
a,b,c,d,e,f,g
'
,
'
,
'
)
结果为:
7