Re: Using instr in substr more than once

On 20 Feb 2002 06:16:55 -0800, leanne.gleitz_at_anthem.com (Leanne Gleitz) wrote:

>I have some data set up in the following manner :
>
>12345*67*8
>9876*54
>456
>
>Is there any way to use a simple select to pull out the data into
>three separate columns :
>
>12345 67 8
>9876 54
>456
>
>I have tried the following:
>
>select substr(data,1,instr(data,'*')-1)
> ,substr(data,instr(data,'*')+1,instr(data,'*',1,2))
> from data_table
>
>but this doesn't seem to work at getting the first 2 separated out.
>
>For example the first one is returning
>
>12345 67*8
>
>Help! Thanks in advance!

Hint : I see only 2 columns in the select statement. Obviously, that one column should have been 3 columns in the first place.

Hth

Sybrand Bakker, Senior Oracle DBA

To reply remove -verwijderdit from my e-mail address Received on Wed Feb 20 2002 - 09:53:13 CST