Re: How do I move space delimited data into new fields with SQL?

Steve J wrote:

> I posted this question in comp.databases.oracle without any response so I'm
> posting it here.
>
> I need to move the move the above data using SQL into new fields that
> have been created in the same table
> Newfield1 = FTMYFLXA04W
> Newfield2 = VPI0043
> Newfield3 = VCI0184
> Newfield4 = SLT005
> Newfield5 = PRT00001
>
> I'm thinking I need to use INSTR but I don't understand how use it for
> this purpose.
>
> Here's what I'm trying to do in plan english.
>
> Select User1 field
> start in position 1
> Select all characters to space into Newfield1.
> Select all characters from newfield1+space to nextspace into Newfield2
> etc
> etc
>
> Can someone please give me an example of how to accomplish this.
>
> Thanks
>
> Steve

TurkBear has given you the hint you need but one thing that might make it easier during development is to run this first.

column_name := TRANSLATE(column_name, 'A ', 'A|');

replacing the spaces with a vertical bar delimiter and making it easier to see what is happening.

Daniel Morgan Received on Fri Mar 14 2003 - 10:08:17 CST