create function [dbo].[Split2](@input AS Varchar(max) )
Returns @Result Table(Value VARCHAR(MAX))
As
Begin
Declare @str Varchar(20)
Declare @ind Int
If(@input is not null)
Begin
Set @ind = CharIndex(',',@input)--CHARINDEX functions returns the location of a substring in a string. The search is NOT case-sensitive. CHARINDEX( substring, string, [start_position] )
While @ind > 0
Begin
Set @str = SUBSTRING(@input,1,@ind-1)--SUBSTRING functions allows you to extract a substring from a string. SUBSTRING( string, start_position, length )
Set @input = SUBSTRING(@input,@ind+1,LEN(@input)-@ind)
Insert Into @Result values (@str)
Set @ind = CharIndex(',',@input)
End
Set @str = @input
Insert Into @Result values (@str)
End
Return
End
--select * from dbo.[Split2]('1,2,5,7,C1,C2,V1,V3,V4')
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------
create procedure dbo.SequenceSeriesReplaceByHyphen1
@strInputList varchar(max)-- InputString
As
begin
create table dbo.#TempChar(Value Varchar(10))
insert into #TempChar
select Value from dbo.Split2(@strInputList) where Value like '%[^0-9]%';
;With CteSplit(letter, number)
As
(Select LTRIM(RTRIM(LEFT(Value, PATINDEX('%[0-9]%', Value) -1))),
CAST(RIGHT(Value, LEN(Value) - PATINDEX('%[0-9]%', Value) + 1) AS INT)
From #TempChar)
,Cte
As(Select *,RN = number - ROW_NUMBER() OVER(PARTITION BY letter ORDER BY number)
From CteSplit)
,CteFinal
As(Select letter,
startNumber = MIN(number),
endNumber = MAX(number)
From Cte
Group By letter, RN)
Select STUFF((SELECT ',' + letter + '' + CAST(startNumber As varchar(MAX)) +
Case
When startNumber = endNumber Then ''
Else '-' + letter + '' + CAST(endNumber As varchar(MAX))
End
From CteFinal
Order By letter, startNumber, endNumber
For xml Path('')),1, 1, '')
end
--exec SequenceSeriesReplaceByHyphen1 '1,2,3,11,12,13,15,16,20,21,22,C1,C2,C 3,C5,C 6,C 9,72,73,T 1,T 2,T 5,T 99,t100,55,56,57,60,61,V6,V7,V9'
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------