---------------------------------------------------------------- -- Author :fredrickhu(我是小F,向高手学习) -- Date :2009-12-22 15:36:48 -- Version: -- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86) -- Nov 24 2008 13:01:59 -- Copyright (c) 1988-2005 Microsoft Corporation -- Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1) -- ---------------------------------------------------------------- --> 测试数据:[tb] if object_id('[tb]') is not null drop table [tb] go create table [tb]([col] varchar(9)) insert [tb] select 'a,b,c,d' union all select 's,s,s,s,s' --------------开始查询--------------------------select len(col)-len(replace(col,',','')) from [tb] ----------------结果---------------------------- /* ----------- 3 4(2 行受影响)*/
select len(@s,replace(@s,'分隔符','')) 如果分隔符2位,那要.2
写错了 declare @s nvarchar(50) set @s = 'a分隔符b分隔符c分隔符' select (len(@s)-len(replace(@s,'分隔符','')))/3+1
CREATE function f_split(@SourceSql varchar(8000),@StrSeprate varchar(10)) returns @temp table(a varchar(100)) /*--实现split功能的函数*/ as begin declare @i int set @SourceSql=rtrim(ltrim(@SourceSql)) set @i=charindex(@StrSeprate,@SourceSql) while @i>=1 begin insert @temp values(left(@SourceSql,@i-1)) set @SourceSql=substring(@SourceSql,@i+1,len(@SourceSql)-@i) set @i=charindex(@StrSeprate,@SourceSql) end if @SourceSql<>'\' insert @temp values(@SourceSql) return end declare @a varchar(100) select @a = 'a,b,c,d'select 字符串数=count(1) from dbo.f_split(@a,',')字符串数 ----------- 4
select substring('1,2,3,4 ',number, charindex(',','1,2,3,4 '+',',number)-number) from master.dbo.spt_values where type = 'p' and substring(',' + '1,2,3,4 ',number,1)=','------------------ 1 2 3 4 select count(1) from master.dbo.spt_values where type = 'p' and substring(',' + '1,2,3,4 ',number,1)=','------------------ 4
select Get_StrArrayLength(b.CityID),',') as count from t_userinfo a,t_userqx b where a.id=b.Userid 这样提示'Get_StrArrayLength' 不是可以识别的 函数名。
TRY:select b.CityID,[count]=count(c.*) from t_userinfo a join t_userqx b on a.id=b.Userid apply Get_StrArrayLength(b.CityID),',') c
2000 提示 向 substring 函数传递了无效的 length 参数。
select substring('a,b,c,d',number, charindex(',','a,b,c,d '+',',number)-number) from master.dbo.spt_values where type = 'p' and substring(',' + 'a,b,c,d',number,1)=',' select count(1) from master.dbo.spt_values where type = 'p' and substring(',' + 'a,b,c,d',number,1)=','
/*
-----------
3(1 個資料列受到影響
*/
-- Author :fredrickhu(我是小F,向高手学习)
-- Date :2009-12-22 15:36:48
-- Version:
-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([col] varchar(9))
insert [tb]
select 'a,b,c,d' union all
select 's,s,s,s,s'
--------------开始查询--------------------------select len(col)-len(replace(col,',','')) from [tb]
----------------结果----------------------------
/* -----------
3
4(2 行受影响)*/
如果分隔符2位,那要.2
declare @s nvarchar(50)
set @s = 'a分隔符b分隔符c分隔符'
select (len(@s)-len(replace(@s,'分隔符','')))/3+1
returns @temp table(a varchar(100))
/*--实现split功能的函数*/
as
begin
declare @i int
set @SourceSql=rtrim(ltrim(@SourceSql))
set @i=charindex(@StrSeprate,@SourceSql)
while @i>=1
begin
insert @temp values(left(@SourceSql,@i-1))
set @SourceSql=substring(@SourceSql,@i+1,len(@SourceSql)-@i)
set @i=charindex(@StrSeprate,@SourceSql)
end
if @SourceSql<>'\'
insert @temp values(@SourceSql)
return
end declare @a varchar(100)
select @a = 'a,b,c,d'select 字符串数=count(1) from dbo.f_split(@a,',')字符串数
-----------
4
charindex(',','1,2,3,4 '+',',number)-number)
from master.dbo.spt_values
where type = 'p'
and substring(',' + '1,2,3,4 ',number,1)=','------------------
1
2
3
4 select count(1)
from master.dbo.spt_values
where type = 'p'
and substring(',' + '1,2,3,4 ',number,1)=','------------------
4
where a.id=b.Userid
这样提示'Get_StrArrayLength' 不是可以识别的 函数名。
from t_userinfo a join t_userqx b
on a.id=b.Userid apply Get_StrArrayLength(b.CityID),',') c
提示 向 substring 函数传递了无效的 length 参数。
charindex(',','a,b,c,d '+',',number)-number)
from master.dbo.spt_values
where type = 'p'
and substring(',' + 'a,b,c,d',number,1)=','
select count(1)
from master.dbo.spt_values
where type = 'p'
and substring(',' + 'a,b,c,d',number,1)=','