How to format query output

[limester]

Hi,

I have a column where data is seperated by a delimiter, in this case a '\' I am looking for a way to format the output of a query so that I can seperate the data held between the delimiter, for example, if I have the column:
aaa\bbbb\cccccc\dddddd\eeeeeeee

I would like to format it as follows:

aaa :
bbbb :
cccccc :
dddddd :
eeeeeeee :

Any help would be greatly appreciated!

Thanks.

 

[gmmastros]

First, create a user defined function.

CREATE  Function dbo.Split
    (
    @CommaDelimitedFieldNames Varchar(8000), 
    @Character VarChar(20)
    ) 
Returns @Tbl_FieldNames 
Table     (
        Id Integer Identity(1,1),
        FieldName VarChar(100)
        ) 
As 
Begin 
 Set @CommaDelimitedFieldNames = @CommaDelimitedFieldNames + @Character 

 Declare @Pos1 Int
 Declare @pos2 Int
 
 Set @Pos1=1
 Set @Pos2=1

 While @Pos1<Len(@CommaDelimitedFieldNames)
 Begin
 Set @Pos1 = CharIndex(@Character,@CommaDelimitedFieldNames,@Pos1)
 Insert @Tbl_FieldNames Select Cast(Substring(@CommaDelimitedFieldNames,@Pos2,@Pos1-@Pos2) As VarChar(100))
 Set @Pos2=@Pos1+1
 Set @Pos1 = @Pos1+1
 End 
 Return
End

Then, to use it...

Select * 
from   dbo.split('aaa\bbbb\cccccc\dddddd\eeeeeeee', '\')

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom