Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2003

    Unanswered: Remove non-numerics from a string

    I apologize as Iam sure this has been asked and answered before, but the search function keeps erroring out on me.

    How can I remove non-numeric characters from a string (or extract the numeric characters)?

    For example if the string is "Number of calls 9" I would want to return "9". Not all values in the common will have a numeric character.



  2. #2
    Join Date
    Nov 2003
    Provided Answers: 23
    Use the translate function:
    SELECT translate(upper(theField), ''0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ ','0123456789')
    FROM table
    Last edited by shammat; 02-15-06 at 17:51.

  3. #3
    Join Date
    Nov 2003
    For some reason that is still leaving the alpha characters, though from everything I have read on the function it looks like it should work.

    Got it working using this:

    select translate(upper(FIELD),
    '1234567890ABCDEFGHIJKLMNOPQRSTUVWXYZ- ','1234567890') from table

    Thanks for the help!
    Last edited by cdols; 02-15-06 at 19:01.

Posting Permissions

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