Column Splitting Function

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

--Drop Function Dbo.Part

--This function Split the sreing in diffrent parts.

CREATE Function [dbo].[SplitPart]

(@Value Varchar(8000)

,@Part Int

,@Sep Char(2)

)Returns Varchar(8000)

As Begin

Declare @Start Int

Declare @Finish Int

Set @Start=1

Set @Finish=CharIndex(@Sep,@Value,@Start)

While (@Part>1 And @Finish>0)Begin

Set @Start=@Finish+2

Set @Finish=CharIndex(@Sep,@Value,@Start)

Set @Part=@Part-2

End

If @Part>1 Set @Start=Len(@Value)+2 -- Not found

If @Finish=0 Set @Finish=Len(@Value)+2 -- Last token on line

Return SubString(@Value,@Start,@Finish-@Start)

End

Ex. If your column like range Begin Account Number to End Account Number

Totaling

41100001..41100100

42100001..42100050

42200001..42200099

42200125..42200160

42200525..42200560

42200561..42200599

43100001..43100050

43100051..43100099

43100100..43100198

43100199..43100249

43100250..43100275

44100001..44100030

44100031..44100099

And you split in to column Begin Account Number and End Account Number value

Begin Account Number => 41100001 and End Account Number =>41100100 then just call this function like

SELECT Totaling , Dbo.SplitPart (Totaling,1,'..')AS [Begin Account Number] ,

Dbo. SplitPart (Totaling,2,'..')AS [End Account Number]

FROM dbo.[Natural Valley Ltd_$G_L Account]

WHERE [G_L Account Type] = 4 AND [Account Type] = 4

1 and 2 is first part and second part.

Totaling Begin Account Number End Account Number

41100001..41100100 41100001 41100100

42100001..42100050 42100001 42100050

42200001..42200099 42200001 42200099

42200125..42200160 42200125 42200160

42200525..42200560 42200525 42200560

42200561..42200599 42200561 42200599

43100001..43100050 43100001 43100050

43100051..43100099 43100051 43100099

43100100..43100198 43100100 43100198

43100199..43100249 43100199 43100249

43100250..43100275 43100250 43100275

44100001..44100030 44100001 44100030

44100031..44100099 44100031 44100099