Text to Columns function

[DrSmyth]

I've got a text field in a BO report that has 10 ':' characters in it. Between each ':' is a text string that i need to retrieve.

I've been using a combination of SubStr() & Pos() & Length() functions to achieve this but the processing time has started to become rididculous.

Which got me thinking, Is there a similar function to the Text to Columns function that is available in Excel?

The nearest I could think of was doing a replace of all the ':' characters with something that would force a new column. Here's the first stab at a function to achieve this

=Replace(<Narr(Query 1 with Accounts)> , Char(13)&Char(10) ,"")&Replace(<Narr(Query 1 with Accounts)> ,":" ,Char(31))

This function also replaces line feeds and carriage returns, which works.
However, I've chosen a record seperator character (Char(31) from the ASCii characters, which doesn't actually create a new column.

Any suggestions how I could make this work as I'm sure there's a much more efficient way for me to sperate this character string field

 

[DrSmyth]

Nearly forgot I'm using BO full client version 6.5

 

[skrandel]

BO has nothing like this. Why don't you do this in your universe and bring back what you want instead of having the report do all the work.

Steve Krandel
Symantec

 

[DrSmyth]

Hi Steve, I thought that that may be the case. How would i achieve something like this in the universe?

 

[skrandel]

Depends on your database.

Oracle has all the functions you need to substitute characters, substring and index stuff.

Steve Krandel
Symantec

 

[BrianTyler]

There is no 'easy' way to accomplish what you want. Relational DB theory does not allow for the breaking down of fields into components (date/time being the exception to this rule).

The only solution I can see is to create 10 objects in the universe, each constructed by complex SUBSTR statements, but at least this is a one-off operation.

A more technical solution would be to write some form of database procedure in another language to 'parse' the field, creating 10 DB objects.

Brian

 

[DrSmyth]

Cheers gents,

I'm using a DB2 platform, so not sure if there are any specific functions to achieve this. I gave creating some substring universe objects but run time was severely impacted. Think I going to run the output through excel and split the field using some VBA. Seems to be the quickest solution with regard to processing time.

Cheers again for the input