create function get_yourcount(@a varchar(40)) returns int as begin declare @b int set @b=0 while (charIndex(',',@a) > 0) begin set @b=@b + 1 set @a=right(@a,Len(@a) - charIndex(',',@a)) end set @b= 1 + @b return @b end --运行 select ID, value,dbo.get_yourcount(value) from yourtable
如果数据是规范的。就简单了 try: select id,(case len(value) when 2 then 1 when 5 then 2 when 8 then 3 end) as [Count] from yourtable
create function get_yourcount(@a varchar(40))
returns int
as
begin
declare @b int
set @b=0
while (charIndex(',',@a) > 0)
begin
set @b=@b + 1
set @a=right(@a,Len(@a) - charIndex(',',@a))
end
set @b= 1 + @b
return @b
end
--运行
select ID, value,dbo.get_yourcount(value) from yourtable
try:
select id,(case len(value) when 2 then 1 when 5 then 2 when 8 then 3 end) as [Count] from yourtable