-
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
Never Ever Give Up!
-
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;
Aleš Orehek
-
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
Never Ever Give Up!
-
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.
Jurij Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|