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/三
------解决方案--------------------
字段截取 substring 截取值再用case when 比较
------解决方案--------------------
- SQL code
Declare @Tbl Table (ID int, NAME varchar(50))Insert Into @TblSelect 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 '/三' EndFrom @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/二*/