ID NAME
1001 CG14*13.5-85/31-37/113*23W
1002 CG6.5*5.4-190/31-37/248*33W
1003 CG5*5-105/30-32/103.5*10.5V+W
1004 CG5*5-105/30-33/103*10.5
1005 CG6.5*5.3-125/31-37/104*9.8B(V+W)
1006 CG6.5*5.4-190/31-37/114.5*23P想得到下面的结果
ID NAME
1001 CG14*13.5-85/31-37/113*23W/二
1002 CG6.5*5.4-190/31-37/248*33W/二
1003 CG5*5-105/30-32/103.5*10.5V+W/三
1004 CG5*5-105/30-33/103*10.5/三
1005 CG6.5*5.3-125/31-37/104*9.8B(V+W)/二
1006 CG6.5*5.4-190/31-37/114.5*23P/
当两个//间的绝对值小于5时,在对应的NAME后加/三,大于5加/二
例如:CG14*13.5-85/31-37/113*23W
31-37的绝对值是6,大于5,所以结果为:CG14*13.5-85/31-37/113*23W/二
CG5*5-105/30-32/103.5*10.5V+W
30-32的绝对值2,小于5 所以结果为:CG5*5-105/30-32/103.5*10.5V+W/三
1001 CG14*13.5-85/31-37/113*23W
1002 CG6.5*5.4-190/31-37/248*33W
1003 CG5*5-105/30-32/103.5*10.5V+W
1004 CG5*5-105/30-33/103*10.5
1005 CG6.5*5.3-125/31-37/104*9.8B(V+W)
1006 CG6.5*5.4-190/31-37/114.5*23P想得到下面的结果
ID NAME
1001 CG14*13.5-85/31-37/113*23W/二
1002 CG6.5*5.4-190/31-37/248*33W/二
1003 CG5*5-105/30-32/103.5*10.5V+W/三
1004 CG5*5-105/30-33/103*10.5/三
1005 CG6.5*5.3-125/31-37/104*9.8B(V+W)/二
1006 CG6.5*5.4-190/31-37/114.5*23P/
当两个//间的绝对值小于5时,在对应的NAME后加/三,大于5加/二
例如:CG14*13.5-85/31-37/113*23W
31-37的绝对值是6,大于5,所以结果为:CG14*13.5-85/31-37/113*23W/二
CG5*5-105/30-32/103.5*10.5V+W
30-32的绝对值2,小于5 所以结果为:CG5*5-105/30-32/103.5*10.5V+W/三
详细信息如下:declare @MB002 varchar(8000)--存储字段品名
declare @MB003 varchar(8000)--存储字段规格
declare @MB001 char(8000)--存储品号declare @result int --存储数字之间的差
declare change_col cursor for
select MB001,MB002,MB003 from INVMB WHERE MB002='增强片' order by MB001
open change_col
fetch next from change_col into @MB001,@MB002,@MB003
while (@@fetch_status = 0)
begin
set @result = (substring(MB003,charindex(MB003,'/')+1,5))
if (ABS(@result) >5)
begin
update INVMB set MB003 = substring(@MB003,0,len(@MB003)-2)+'/二' where MB001 = @MB001
end
else
begin
update INVMB set MB003 = substring(@MB003,0,len(@MB003)-2)+'/三' where MB001 = @MB001
end
fetch next from change_col into @MB001,@MB002,@MB003
end
close change_col
deallocate change_col
Declare @Tbl Table (ID int, NAME varchar(50))
Insert Into @Tbl
Select 1001, 'CG14*13.5-85/31-37/113*23W'
Union All Select 1002, 'CG6.5*5.4-190/31-37/248*33W'
Union All Select 1003, 'CG5*5-105/30-32/103.5*10.5V+W'
Union All Select 1004, 'CG5*5-105/30-33/103*10.5'
Union All Select 1005, 'CG6.5*5.3-125/31-37/104*9.8B(V+W)'
Union All Select 1006, 'CG6.5*5.4-190/31-37/114.5*23P';With Tbl(ID, Num1, Num2)
As(
Select ID, CAST(SUBSTRING(SUBSTRING(NAME, CHARINDEX('/', NAME) + 1, 5), 1, 2) As Int)
, CAST(SUBSTRING(SUBSTRING(NAME, CHARINDEX('/', NAME) + 1, 5), 4, 2) As Int)
From @Tbl
)
Update A Set A.Name = A.Name + Case When ABS(B.Num1-B.Num2) > 5 Then '/二' Else '/三' End
From @Tbl A Inner Join Tbl B On A.ID = B.IDSelect * From @Tbl/*
ID NAME
----------- --------------------------------------------------
1001 CG14*13.5-85/31-37/113*23W/二
1002 CG6.5*5.4-190/31-37/248*33W/二
1003 CG5*5-105/30-32/103.5*10.5V+W/三
1004 CG5*5-105/30-33/103*10.5/三
1005 CG6.5*5.3-125/31-37/104*9.8B(V+W)/二
1006 CG6.5*5.4-190/31-37/114.5*23P/二*/