Re: help with regexp_substr using Oracle XE with Windows XP Home

From: Maxim Demenko <mdemenko_at_gmail.com>
Date: Sat, 24 Feb 2007 17:01:54 +0100
Message-ID: <45E06172.2030306@gmail.com>

andrewbgilford_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/functions116.htm
>
> 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 Received on Sat Feb 24 2007 - 10:01:54 CST