Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2004
    Posts
    66

    Unanswered: Retrive Only Numeric Fields from a String

    Hi,

    I have a input string . I need only the number from this field using REPLACE or TRANSLATE or any function in the SELECT statement.

    eg,

    the input string is :
    ' 1 :: 2 3 Adfre 34 * & )( -= bd '

    It should return only the numeric field
    12334


    I tried with
    SELECT TRANSLATE(string,TRANSLATE(string,'0123456789',' '),'') from dual;

    -- But it's not working properly.

    Any help plz .......
    himridul

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Retrive Only Numeric Fields from a String

    It just needs a slight tweak:

    SELECT TRANSLATE(string,'0'||TRANSLATE(string,'0123456789 ',' '),'0') from dual;

    (You need to avoid passing a NULL 3rd parameter.)

  3. #3
    Join Date
    Jan 2004
    Posts
    66
    Thanks Andrewst ,

    It's working fine.

    Thanks again
    himridul

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •