Selecting Odd and even digit values from a string
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: Selecting Odd and even digit values from a string

  1. #1
    Join Date
    Oct 2002
    Location
    Singapore
    Posts
    16

    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!

  2. #2
    Join Date
    Oct 2002
    Location
    Ljubljana,Slovenia
    Posts
    28
    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

  3. #3
    Join Date
    Oct 2002
    Location
    Singapore
    Posts
    16
    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!

  4. #4
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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
  •  


Click Here to Expand Forum to Full Width