SQLTeam.com | Weblogs | Forums

Sql server: output as per requirement

sql2008

#1

input: '1,2,4,5,C1,C2,C5,C7,C8,C9,T1,T5,T6,T10'
output: 1-2,4-5,C1-C2,C5,C7-C9,T1,T5-T6,T10

here when we give input like, comma separated string of
different series then output should be like each series with hyphen and
separated by comma only output should not be in table format.


#2

Tell me more about this. What is your table format? What have you already tried? Why are you doing this in SQL?


#3
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'
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------