OraQA

Oracle Question and Answer

  • Do you have a solution to a problem? Do you have an unanswered question? Login and share it with the Oracle community. More...

Oracle News


Entries RSS feed

Comments RSS feed

How to seperate character data from numeric data

January 30th, 2006 By Eddie Awad

You have an alphanumeric column in your database and you want to split this column into two, one column containing the character data only, and a second column containing the numeric data only. For example, you want ‘hello5623′ to be ‘hello’ and ’5623′.

This can be done in SQL using the two functions TRANSLATE and REPLACE:

HR@XE> select
  2      replace
  3      (
  4          translate(data,'0123456789','0000000000'),'0'
  5      ) myString,
  6      to_number
  7      (
  8          replace
  9          (
 10              translate
 11              (
 12                  lower(data),
 13                  'abcdefghijklmnopqrstuvwxyz',rpad('z',26,'z')
 14              ),'z'
 15          )
 16      ) myNumber
 17  from (
 18      select 'hello5623' data
 19      from dual
 20      )
 21  /

MYSTR   MYNUMBER
----- ----------
hello       5623

To get the number portion of the string, the trick is to use TRANSLATE to transform all characters to one character (z in this example) and then remove it using REPLACE.

To get the character portion of the string, the trick is to use TRANSLATE to transform all numbers to one number (0 in this example) and then remove it using REPLACE.

2 Responses to “How to seperate character data from numeric data”

  1. Claudiu Ariton Says:

    I think it is better to use

    select replace(translate(data,
    replace(translate(data,’0123456789′,’0000000000′),’0′,null),rpad(‘z’,length(data),’z’)),’z’,null)
    from
    (select ‘hello5623′ data from dual)

    to extract the numeric data

    Best regards,
    Claudiu Ariton

  2. gamyers Says:

    Regular expressions in 10G make this a whole lot simpler :

    select orig,
    regexp_replace(orig,’[0-9]‘,”,1
    regexp_replace(orig,’[a-zA-Z]‘,’
    from (select ‘helloe12te3st’ orig from dual);
    /

    ORIG
    ————-
    REGEXP_REPLACE(ORIG,’[0-9]‘,”,1,0,’NM’)
    ——————————————–
    REGEXP_REPLACE(ORIG,’[A-ZA-Z]‘,”,1,0,’NM’)
    ——————————————–
    helloe12te3st
    helloetest
    123

Leave a Reply

You must be logged in to post a comment.

RSS feed for comments on this question