Selecting Odd and even digit values from a string

Hi,

I would like to know a simple way of selecting the odd and even digits from a string of 48 charaters.

Say I have a string for example '1p1p2p3p4p5p4e3e2e1e' of 20 characters. I want to select the odd digit values '1123454321' and
even digit values 'ppppppeeee'.

I tried using substr('str',1,1) || substr('str',3,1) and so on for odd digit values and similarly for even digit values.
I don't know if there is any simple method other than using the substr.

Expecting the expert comments on this.

Thanks in adv.

==> Eeswar

:confused:

IN PL/SQL you can try this

DECLARE
v_string VARCHAR2(255) := '1p1p2p3p4p5p4e3e2e1e';
v_odd VARCHAR2(255);
v_even VARCHAR2(255);
BEGIN
FOR r IN 1..LENGTH( v_string)
LOOP
IF MOD( r,2) <> 0 THEN
v_odd := NVL( v_odd,'' ) || SUBSTR( v_string, r, 1 );
ELSE
v_even := NVL( v_even,'' ) || SUBSTR( v_string, r, 1 );
END IF;
END LOOP;
DBMS_OUTPUT.PUT_LINE( ' odd:' || v_odd);
DBMS_OUTPUT.PUT_LINE( 'even:' || v_even);
END;

AOrehek..Thanx for your reply.

But, I want to do this using single select statement without using pl/sql.

Is there any function in Oracle OR is there any way to get the even and odd applying different Oracle functions.

Thanks...Eeswar

Quote:

---

Originally posted by eeswar
But, I want to do this using single select statement without using pl/sql.

---

I don't think there is any way to do this only with SQL and with supplied built in functions (appart from using chained SUBSTR() as you have described).

Well, actually there is one way: get an Oracle10g beta or wait until 10g is officially released - there is full support for regular expressions in 10g, which will make your problem a peace of cake to solve with SQL. :D