T-SQL中一个字符串处理的有关问题

T-SQL中一个字符串处理的问题
我需要处理一列字符串，输入的字符串中，形如 "属性1-属性2~属性3-属性4/属性5*属性6".
其中字符串中的连接符只可能为-,~,*,/这四种。在字符串中，有n个连接符，就有n+1个属性，属性最多为6个。属性中不可能有连接符。
需求是，需要把属性的序号替换成数字，比如上边的例子输出为：1-2~3-4/5*6

如果输入是："属性1-属性2~属性3-属性4".
则输出为：1-2~3-4

不知道我说明白没，有没有达人给个思路？


[解决办法]

SQL code

---,~,*,/DECLARE @STR VARCHAR(200)DECLARE @RESULT VARCHAR(200)DECLARE @POS INTDECLARE @ID INTSELECT @STR = '外径(mm)*壁厚(mm)-钢级-扣型-特殊要求'SELECT @RESULT = ''SELECT @ID = 1SELECT @POS = MIN(ID) FROM (SELECT CHARINDEX('-',@STR) AS ID UNION ALLSELECT CHARINDEX('~',@STR) AS ID UNION ALLSELECT CHARINDEX('*',@STR) AS ID UNION ALLSELECT CHARINDEX('/',@STR) AS ID ) TWHERE ID > 0WHILE @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 > 0ENDSELECT @RESULT = @RESULT + LTRIM(@ID)SELECT @RESULT
[解决办法]
SQL code
if object_id('dbo.get_number2') is not nulldrop function dbo.get_number2gocreate function dbo.get_number2(@s varchar(100))returns varchar(100)asbegin    while patindex('%[^+*/^~.-]%',@s) > 0    begin    set @s=stuff(@s,patindex('%[^+*/^~-]%',@s),1,'')    end    return @sendgocreate function [dbo].[funsplitcharV1.0]   (   @stra nvarchar(100)   )returns @result table (id int identity(1,1),item nvarchar(1))asbegin   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 ''    returnenddeclare @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*/
[解决办法]
SQL code
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)))ltunion allselect flg+1,convert(varchar(20),t+LTRIM(flg+1)+substring(lt,patindex('%[-*/~]%',lt),1)),convert(varchar(200),RIGHT(lt,len(lt)-patindex('%[-*/~]%',lt))) from ctewhere 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 行受影响)*/