求教各位大侠:菜鸟问题如下:表 TA
id int a1 varchar(20)
2 1,0,
3 0,1,25,0
4 1,30,1,0将a1 字段中大于10的数字替换成‘0’ .
预期结果: id a1
2 1,0
3 0,1,0,0
4 1,0,1,0该查询语句如何写? 先谢了!
id int a1 varchar(20)
2 1,0,
3 0,1,25,0
4 1,30,1,0将a1 字段中大于10的数字替换成‘0’ .
预期结果: id a1
2 1,0
3 0,1,0,0
4 1,0,1,0该查询语句如何写? 先谢了!
IF OBJECT_ID('[tb]') IS NOT NULL DROP TABLE [tb]
GO
CREATE TABLE [tb]([id] INT,[a1] VARCHAR(20))
INSERT [tb]
SELECT 2,'1,0' UNION ALL
SELECT 3,'0,1,25,0' UNION ALL
SELECT 4,'1,30,1,0'
GO--> 测试语句:
if object_id('f_str',N'FN') is not null drop function f_str
go
create function f_str(@a varchar(20))
returns varchar(20)
as
begin
declare @b varchar(10),@s varchar(20)
set @a=@a+','
while charindex(',',@a)>0
begin
set @b=left(@a,charindex(',',@a)-1)
set @b= case when @b>10 then '0' else @b end
set @s=isnull(@s+',','')+@b
set @a=substring(@a,charindex(',',@a)+1,len(@a)-charindex(',',@a))
end
return @s
end
go
SELECT id,dbo.f_str(a1) FROM [tb]
/*
id
----------- --------------------
2 1,0
3 0,1,0,0
4 1,0,1,0(3 行受影响)*/