Column Splitting Function

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

Ebook Download
View all
Learn
View all