日期:2014-05-18 浏览次数:20509 次
---,~,*,/ DECLARE @STR VARCHAR(200) DECLARE @RESULT VARCHAR(200) DECLARE @POS INT DECLARE @ID INT SELECT @STR = '外径(mm)*壁厚(mm)-钢级-扣型-特殊要求' SELECT @RESULT = '' SELECT @ID = 1 SELECT @POS = MIN(ID) FROM (SELECT CHARINDEX('-',@STR) AS ID UNION ALL SELECT CHARINDEX('~',@STR) AS ID UNION ALL SELECT CHARINDEX('*',@STR) AS ID UNION ALL SELECT CHARINDEX('/',@STR) AS ID ) T WHERE ID > 0 WHILE @POS > 0 BEGIN SELECT @RESULT = @RESULT + LTRIM(@ID) + SUBSTRING(@STR,@POS,1) SELECT @ID = @ID + 1 SELECT @STR = SUBSTRING(@STR,@POS + 1,LEN(@STR)) SELECT @POS = MIN(ID) FROM (SELECT CHARINDEX('-',@STR) AS ID UNION ALL SELECT CHARINDEX('~',@STR) AS ID UNION ALL SELECT CHARINDEX('*',@STR) AS ID UNION ALL SELECT CHARINDEX('/',@STR) AS ID ) T WHERE ID > 0 END SELECT @RESULT = @RESULT + LTRIM(@ID) SELECT @RESULT
------解决方案--------------------
if object_id('dbo.get_number2') is not null drop function dbo.get_number2 go create function dbo.get_number2(@s varchar(100)) returns varchar(100) as begin while patindex('%[^+*/^~.-]%',@s) > 0 begin set @s=stuff(@s,patindex('%[^+*/^~-]%',@s),1,'') end return @s end go create function [dbo].[funsplitcharV1.0] ( @stra nvarchar(100) ) returns @result table (id int identity(1,1),item nvarchar(1)) as begin declare @nc int select @nc=1 while @nc<=len(@stra) begin insert @result select substring(@stra,@nc,1) select @nc=@nc+1 end insert @result select '' return end declare @t varchar(200) set @t='外径40(mm)*壁厚25(mm)-钢级35-扣型10+特殊12/特殊钢3' declare @sql varchar(200) set @sql='' select @sql=@sql+ltrim(id)+item from dbo.[funsplitcharV1.0](dbo.[get_number2](@t)) select @sql /* 1*2-3-4+5/6 */
------解决方案--------------------
declare @t varchar(200) set @t='外径40(mm)*壁厚25(mm)-钢级35-扣型10+特殊12/特殊钢3' ;with cte as( select 1 as flg,convert(varchar(20),'1'+substring(@t,patindex('%[-*/~]%',@t),1)) as t,convert(varchar(200),RIGHT(@t,len(@t)-patindex('%[-*/~]%',@t)))lt union all select flg+1,convert(varchar(20),t+LTRIM(flg+1)+substring(lt,patindex('%[-*/~]%',lt),1)),convert(varchar(200),RIGHT(lt,len(lt)-patindex('%[-*/~]%',lt))) from cte where patindex('%[-*/~]%',lt)>0 )select t+LTRIM(flg+1) from cte a where not exists(select 1 from cte where t>a.t) /* -------------------------------- 1*2-3-4/5 (1 行受影响) */