一种可能的方法是使用以下语句 STRING_SPLIT() （要拆分逗号分隔的数字）， ROW_NUMBER() （定义孤岛和缺口）和 CONCAT_WS() 以及 STRING_AGG() （对于字符串聚合）：

DECLARE @a VARCHAR(50)= '07071318_G08(1,2,3,4,8,9,11)';

SELECT STRING_AGG([value], ',') WITHIN GROUP (ORDER BY [rn]) AS [value]
FROM (
   SELECT MIN([value]) AS [rn], CONCAT_WS('-', MIN([value]), CASE WHEN MAX([value]) > MIN([value]) THEN MAX([value]) END) AS [value]
   FROM (
      SELECT 
         TRY_CONVERT(int, [value]) AS [value],
         ROW_NUMBER() OVER (ORDER BY TRY_CONVERT(int, [value])) AS [rn]
      FROM STRING_SPLIT(SUBSTRING(@a, CHARINDEX('(', @a) + 1, CHARINDEX(')', @a) - CHARINDEX('(', @a) - 1), ',')
   ) t1
   GROUP BY [value] - [rn]
) t2

结果：

value
1-4,8-9,11

既然你在2017年，你就可以使用 string_agg() ，但首先我们执行 Gaps-and-Islands 例子

DECLARE @a VARCHAR(50)= '07071318_G08(1,2,3,4,8,9,11)';

Select MyNewString = string_agg(value,',') WITHIN GROUP ( ORDER BY min_value )
 From (
        Select value=concat(min(value),IIF(min(value)=max(value),'',concat('-',max(value))))
              ,min_value = min(value)
         From (
                SELECT value = try_convert(int,value)
                      ,grp = value - row_number()  over(order by try_convert(int,value))
                 from string_split(SUBSTRING(@a, CHARINDEX('(', @a) + 1, CHARINDEX(')', @a) - CHARINDEX('(', @a) - 1),',')
              ) a
         Group by Grp 
       ) A

退货

MyNewString
1-4,8-9,11

注意：如果复制字符串中的值，如…3，3，3。。。你可能得改用 dense_rank() 而不是 row_number() 编辑-2014版

DECLARE @a VARCHAR(50)= '07071318_G08(1,2,3,4,8,9,11)';

Select NewValue = Stuff((Select ',' +NewValue
                          From (
                                Select NewValue = concat(min(RetVal),IIF(max(RetVal)=min(RetVal),'',concat('-',max(RetVal))))
                                      ,MinValue = min(RetVal)
                                 From (
                                        Select RetSeq = row_number() over (order by try_convert(int,ltrim(rtrim(B.i.value('(./text())[1]', 'varchar(max)')))))
                                              ,RetVal = try_convert(int,ltrim(rtrim(B.i.value('(./text())[1]', 'varchar(max)'))))
                                        From  (Select x = Cast('<x>' + replace(SUBSTRING(@a, CHARINDEX('(', @a) + 1, CHARINDEX(')', @a) - CHARINDEX('(', @a) - 1),',','</x><x>')+'</x>' as xml).query('.')) as A 
                                        Cross Apply x.nodes('x') AS B(i)
                                      ) a
                                 Group By RetVal-RetSeq    
                               ) a1
                           Order by MinValue
                          For XML Path ('')),1,1,'')