On Feb 24, 4:01 pm, Maxim Demenko <mdeme..._at_gmail.com> wrote:
> andrewbgilf..._at_hotmail.co.uk schrieb:
>
>
>
> > Hello - this is my first post to this professional newsgroup. If this
> > is the wrong newsgroup please e-mail me and not flame me if I maked a
> > mistake.
>
> > In Oracle what I want from my sql is the following results.
>
> > first second third fourth
> > ------ ---------- -------------- ---------
> > first second third word fourth.
>
> > What I have written is
>
> > SELECT REGEXP_SUBSTR('/first/second/third word/fourth./','/[a-z .]*/',
> > 1,1) first,
> > REGEXP_SUBSTR('/first/second/third word/fourth./','/[a-z .]*/',
> > 1,2) second,
> > REGEXP_SUBSTR('/first/second/third word/fourth./','/[a-z .]*/',
> > 1,3) third,
> > REGEXP_SUBSTR('/first/second/third word/fourth./','/[a-z .]*/',
> > 1,4) fourth
> > FROM DUAL
>
> > but the occurrence value (the 1 or 2 or 3 or 4) in the manual from
>
> >http://download-west.oracle.com/docs/cd/B14117_01/server.101/b10759/f...
>
> > doesn't work properly. I think it does work but it is my sql that is
> > wrong as I am a beginner. Can someone help me with the correct
> > regexp_substr commands?
>
> > Thank you
>
> > Andy
>
> Will it suffice for your purposes?
>
> SQL> with t as (
> 2 select '/first/second/third word/fourth./' s
> 3 from dual)
> 4 select
> 5 regexp_substr(s,'[a-z .]+',1,1) first,
> 6 regexp_substr(s,'[a-z .]+',1,2) second,
> 7 regexp_substr(s,'[a-z .]+',1,3) third,
> 8 regexp_substr(s,'[a-z .]+',1,4) fourth
> 9 from t;
>
> FIRST SECOND THIRD FOURTH
> ----- ------ ---------- -------
> first second third word fourth.
>
> Best regards
>
> Maxim
Thank you Maxim - this works really well.
Andy
Received on Sun Feb 25 2007 - 01:55:13 CST
