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