Split based on the input

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » Split based on the input

Split based on the input

Rate Topic

Display Mode

Topic Options

Author

Message

yuvipoy

Posted Wednesday, January 02, 2013 3:22 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 2:53 AM
Points: 111, Visits: 517

hi
how to split the string based on the input

say starting is 1 and next is also 1 the difference is 1
say starting is 2 and next is also 1 the difference is 1
say starting is 3 and next is also 5 the difference is 5

declare @input varchar(1000)='1,1,5,5,6,2'

start end,difference
1,1,1
2(start+difference),2,1
3(start+difference),7,5
8(start+difference),12,5
13(start+difference),18,6
19(start+difference),20,2

Thanks!

Post #1401733

yuvipoy

Posted Wednesday, January 02, 2013 5:37 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 2:53 AM
Points: 111, Visits: 517

hi,

declare @input varchar(1000)='1,1,5,5,6,2'

start, difference
1,1
2,1
3,5
8,5
13,6
19,2

Go
declare @input varchar(1000)='5,3,2'
start, difference
1,5
6,3
9,2

Post #1401796

yuvipoy

Posted Wednesday, January 02, 2013 6:28 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 2:53 AM
Points: 111, Visits: 517

I got it thanks!!

Create Function [dbo].[Splited_Values]
(
@CommaSeparatedString varchar(8000)
)
RETURNS @Table Table ( startid int,ID int)
Begin
Declare @TempStr varchar(8000) ,@tempval int=1
Set @TempStr = @CommaSeparatedString + ','
While Len(@TempStr) > 0
Begin
Insert Into @Table Select @tempval,SubString(@TempStr,1,CharIndex(',',@TempStr)-1)
Set @tempval= @tempval+SubString(@TempStr,1,CharIndex(',',@TempStr)-1)
Set @TempStr = Right(@TempStr,Len(@TempStr)-CharIndex(',',@TempStr))
End
Return
End

Post #1401810

« Prev Topic | Next Topic »

Permissions