Breaking out an Array of Data in One Column?

[Strife]

I have a large oracle table that has data stored for each day of the week all in one column.

For example, the ratebyday column that has the rate for each day of the week. Is there a process to easily split out the data into 7 columns in a query just using the delimiter? I would use left() or something but each rate has a different number of values.
i.e. one person's ratebyday 0.364583;0.364583;0.364583;0.364583;0.364583;0.364 583;0.364583
another person's 0.33;0.33;0.33;0.33;0.33;0.33;0.33.

Can I use the semicolon without exporting to text then reimporting by using a command in a query? I will be doing a daily process with this data so I'm trying to look for an alternative then manually importing/exporting to text and back into access everyday if it's possible. The text file is also ~54mb because of the amount of data if I did have to do a manual export. Thanks.

 

[FoFa]

Split is the function you want I believe.
But you may have to use vba to separate into columns.

 

[DCrake]

Split is the best approach to take. Make a small function some thing along the lines of

Public Function PymtRate(StringOfRates As string, nIndex as Integer) As String


Dim TmpArray(6)

TmpArray = Split(StringOfRates,";")

PymtRate = TmpArray(nIndex-1)

End Function

In simple terms you pass the long string of rates along with the day you want to extract. The split function places all 7 values into a zero based single dimension array. (0 - 6)

Then the function returns the value found in the array index based on the value passed to it in the nIndex minus 1

So

Day1RateymtRate([Yourfield],2) would yield

2.0 if the contents of [YourField] Were "1.0;2.0;3.0;4;0;5.0;6.0;7.0"

 

[Strife]

Thanks so much DCrake. It worked nearly perfectly, all I had to do was changed TmpArray to

Dim TmpArray as Variant

because I was getting the error 'Can't Assign an Array' and it looks like you have to define the variable as a Variant to fix it. But I tested it and it works perfect now, thanks again.