declare @sql varchar(100) set @sql='一分局' select isnull((len(@sql)-len(replace(@sql,',',''))+1),0)
select isnull(len(字段)-len(replace(字段,',','')),0) as 单位数 from tab
declare @sql varchar(100) set @sql=null select isnull((len(@sql)-len(replace(@sql,',',''))+1),0) set @sql='一分局' select isnull((len(@sql)-len(replace(@sql,',',''))+1),0) set @sql='一分局,二分局' select isnull((len(@sql)-len(replace(@sql,',',''))+1),0) set @sql='一分局,二分局,三分局' select isnull((len(@sql)-len(replace(@sql,',',''))+1),0)----------- 0(1 行受影响) ----------- 1(1 行受影响) ----------- 2(1 行受影响) ----------- 3(1 行受影响)
create function f_get(@col varchar(100)) returns int as begin declare @I int set @i = 0 while charindex(',',@col) > 0 begin set @i = @i + 1 set @col = stuff(@col,1,charindex(',',@col),'') end return @I endgo select col,dbo.f_get(col) from tablename
select 单位数=case when 字段 is null then 0 else len(字段)-len(replace(replace(字段,',',''),' ',''))+1 end from tb
select isnull(len(字段)-len(replace(字段,',',''))+ 1,0) as 单位数 from tab
如果空值为''declare @sql varchar(100) set @sql='' select case @sql when '' then 0 else isnull((len(@sql)-len(replace(@sql,',',''))+1),0)end
找遍了SQL 的字符串函数,好像没有一个函数是 计算特殊字符出现的次数的。ASCII NCHAR SOUNDEX CHAR PATINDEX SPACE CHARINDEX REPLACE STR DIFFERENCE QUOTENAME STUFF LEFT REPLICATE SUBSTRING LEN REVERSE UNICODE LOWER RIGHT UPPER LTRIM RTRIM 建议总结一下规律,部门的长度是否有规律,能否用 len 来计算。
create table t(a varchar(500)) go insert t select '一分局' insert t select '一分局,二分局' insert t select '一分局,二分局,三分局' insert t select '一分局,二分局,三分局,四分局' insert t select '一分局,二分局,三分局,四分局,五分局,一、六分局' insert t select '' goselect 单位数=case when a is null or len(a)=0 then 0 else isnull((len(a)-len(replace(a,',',''))+1),0) end from tdrop table t
set @sql='一分局'
select isnull((len(@sql)-len(replace(@sql,',',''))+1),0)
select isnull(len(字段)-len(replace(字段,',','')),0) as 单位数
from tab
declare @sql varchar(100)
set @sql=null
select isnull((len(@sql)-len(replace(@sql,',',''))+1),0)
set @sql='一分局'
select isnull((len(@sql)-len(replace(@sql,',',''))+1),0)
set @sql='一分局,二分局'
select isnull((len(@sql)-len(replace(@sql,',',''))+1),0)
set @sql='一分局,二分局,三分局'
select isnull((len(@sql)-len(replace(@sql,',',''))+1),0)-----------
0(1 行受影响)
-----------
1(1 行受影响)
-----------
2(1 行受影响)
-----------
3(1 行受影响)
returns int
as
begin
declare @I int
set @i = 0
while charindex(',',@col) > 0
begin
set @i = @i + 1
set @col = stuff(@col,1,charindex(',',@col),'')
end
return @I
endgo
select col,dbo.f_get(col)
from tablename
else len(字段)-len(replace(replace(字段,',',''),' ',''))+1
end
from tb
from tab
set @sql=''
select case @sql when '' then 0 else isnull((len(@sql)-len(replace(@sql,',',''))+1),0)end
CHAR PATINDEX SPACE
CHARINDEX REPLACE STR
DIFFERENCE QUOTENAME STUFF
LEFT REPLICATE SUBSTRING
LEN REVERSE UNICODE
LOWER RIGHT UPPER
LTRIM RTRIM
建议总结一下规律,部门的长度是否有规律,能否用 len 来计算。
go
insert t select '一分局'
insert t select '一分局,二分局'
insert t select '一分局,二分局,三分局'
insert t select '一分局,二分局,三分局,四分局'
insert t select '一分局,二分局,三分局,四分局,五分局,一、六分局'
insert t select ''
goselect 单位数=case when a is null or len(a)=0 then 0
else isnull((len(a)-len(replace(a,',',''))+1),0)
end
from tdrop table t