Re: Using instr in substr more than once

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!

instr takes 4 params, the 3rd (?) being the n'th occurrence, so you could have:

select

  substr(col,instr(col,'*',1))
  substr(col,instr(col,'*',2))
  substr(col,instr(col,'*',3))

hth
connor

-- 
==============================
Connor McDonald

http://www.oracledba.co.uk

"Some days you're the pigeon, some days you're the statue..."