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

"Steve J" <dafella_at_swbell.net> 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
>
>

What 'above data' ?
Anyway, INSTR is well documented in the Sql manual - go to http://tahiti.oracle.com and search

In general:
 Instr(<string or field>,' ',1,1) will return the position of the FIRST space character ( that why the second 1 in the function)
so a
Newfield1 = substr(<string or field>,1, Instr(<string or field>,' ',1,1) -1)  will make Newstring contain the first group of characters before the first space in the string or field..

You can take it from there

hth Received on Fri Mar 14 2003 - 08:58:15 CST