CODEWITHSUNDEEP
sqloracle-databasenumber-formatting
Mahesh Karpe
Mahesh Karpe

Reputation: 21

How to replace numbers in an alphanumeric string with words

I have a table named department_details with a column dept_id which contains values like

10_prod
20_r&d
80_sales

etc. I want a query which will give me output like 

ten_prod
twenty_r&d
eighty_sales

etc.

Upvotes: 1

Views: 459

Answers (2)

CR7SMS
CR7SMS

Reputation: 2584

You could do something like this:

with list1 as
(select '10_prod' as val from dual union
select '20_randd' as val from dual union
select '80_sales' as val from dual)

select a.*,
      to_char(to_date(substr(val,1,2),'j'), 'jsp')||substr(val,3,20) as text_val 
from list1 a

Upvotes: 0

Littlefoot
Littlefoot

Reputation: 142713

Here's one option:

SQL> with test (col) as
  2    (select '10_prod'  from dual union all
  3     select '20_r&d'   from dual union all
  4     select '80_sales' from dual
  5    )
  6  select col,
  7    regexp_substr(col, '^\d+') num,
  8    to_char(to_date(substr(col, 1, instr(col, '_') - 1), 'j'), 'jsp') wrd,
  9    --
 10    to_char(to_date(substr(col, 1, instr(col, '_') - 1), 'j'), 'jsp') ||
 11    substr(col, instr(col, '_')) result
 12  from test;

COL      NUM                              WRD        RESULT
-------- -------------------------------- ---------- --------------------
10_prod  10                               ten        ten_prod
20_r&d   20                               twenty     twenty_r&d
80_sales 80                               eighty     eighty_sales

SQL>

What does it do (step-by-step, so that you could follow it):

  • lines #1 - 5: sample data
  • line #7: one way to extract the number from the beginning of the string (using regular expressions)
  • line #8: another way (using substr + instr; probably better). It - additionally - converts it to date using the 'J' format and to character using the JSP format. This is the usual way of spelling numbers
  • lines #10 - 11: combine spelled number (line #10) with the rest of the string (line #11)

Upvotes: 5

Related Questions