Skip to Main Content

Breadcrumb

Dev Live Dev Intro

This month we are celebrating Developers at AskTOM. We welcome Developers of all levels of experience to join us at our FREE Developer Live events coming in August. Just click on the left to register today! If you are brand new to Database Technology, then we also have got you covered. Just click on the right for your comprehensive FREE training program to kick start your Oracle Database Development journey!

Question and Answer

Connor McDonald

Thanks for the question, Akshay.

Asked: September 15, 2017 - 1:55 pm UTC

Answered by: Connor McDonald - Last updated: December 14, 2017 - 7:52 am UTC

Category: SQL - Version: 10g

Viewed 1000+ times

You Asked

I want output as:

numbers string
101 One zero one
102 One zero two
851 eight five one
9856 nine eight five six
356 three five six
748 seven four eight
254 two five four


How can i get string in this way.

and we said...

You might see some examples using the JSP format to spell a number, eg

SQL> select to_char(to_date('7','J'),'JSP') from dual;

TO_CH
-----
SEVEN


but that wont work here, because zero is not allowed

SQL> select to_char(to_date('0','J'),'JSP') from dual;
select to_char(to_date('0','J'),'JSP') from dual
                       *
ERROR at line 1:
ORA-01854: julian date must be between 1 and 5373484


So we'll just use a simple CASE statement, and "connect by level" to cycle through each character

SQL> select
  2    case x
  3      when '0' then 'zero'
  4      when '1' then 'one'
  5      when '2' then 'two'
  6      when '3' then 'three'
  7      when '4' then 'four'
  8      when '5' then 'five'
  9      when '6' then 'six'
 10      when '7' then 'seven'
 11      when '8' then 'eight'
 12      when '9' then 'nine'
 13    end
 14  from ( select '3' x from dual ) ;

CASEX
-----
three

SQL>
SQL>      select
  2    case x
  3      when '0' then 'zero'
  4      when '1' then 'one'
  5      when '2' then 'two'
  6      when '3' then 'three'
  7      when '4' then 'four'
  8      when '5' then 'five'
  9      when '6' then 'six'
 10      when '7' then 'seven'
 11      when '8' then 'eight'
 12      when '9' then 'nine'
 13    end
 14  from (
 15    select substr('123',rownum,1) x
 16    from dual
 17    connect by level <= 3
 18    ) ;

CASEX
-----
one
two
three


So now we just need to incorporate that into our table of values. Here's how we can get each element within each row of a table

SQL> create or replace type string_list is table of varchar2(20);
  2  /

Type created.

SQL>
SQL> create table t ( x int );

Table created.

SQL>
SQL> insert into t values (101);

1 row created.

SQL> insert into t values (456);

1 row created.

SQL> insert into t values (789);

1 row created.

SQL>
SQL> select *
  2  from t,
  3       table(cast(multiset(
  4          select substr(to_char(t.x),rownum,1)
  5          from dual
  6  connect by level <= length(to_char(t.x))) as string_list)
  7  );

         X COLUMN_VAL
---------- ----------
       101 1
       101 0
       101 1
       456 4
       456 5
       456 6
       789 7
       789 8
       789 9

9 rows selected.


so now we just incorporate our case statement and we're nearly there

SQL> select
  2    x,
  3    case digit
  4      when '0' then 'zero'
  5      when '1' then 'one'
  6      when '2' then 'two'
  7      when '3' then 'three'
  8      when '4' then 'four'
  9      when '5' then 'five'
 10      when '6' then 'six'
 11      when '7' then 'seven'
 12      when '8' then 'eight'
 13      when '9' then 'nine'
 14    end str
 15  from (
 16    select x, column_value digit
 17    from t,
 18         table(cast(multiset(
 19            select substr(to_char(t.x),rownum,1)
 20            from dual
 21    connect by level <= length(to_char(t.x))) as string_list)
 22    )
 23  )
 24
SQL>
SQL>
SQL> /

         X STR
---------- -----
       101 one
       101 zero
       101 one
       456 four
       456 five
       456 six
       789 seven
       789 eight
       789 nine

9 rows selected.



Now we need to aggregate those strings - on 11g upwards you can use LISTAGG but since you're on 10g you'll need to roll your own. See the examples in this question for details.

https://asktom.oracle.com/pls/apex/asktom.search?tag=stragg#16551777586484

Then it will just be

select x, stragg(str)
from
( ... the above )
group by x

and you rated our response

  (1 rating)

Is this answer out of date? If it is, please let us know via a Review

Reviews

An alternative without string aggregation

December 13, 2017 - 5:14 pm UTC

Reviewer: Kim Berg Hansen from Middelfart, Denmark

Hiya,

Just for fun: an alternative without using string aggregation:

with n as (
   select 101 num from dual union all
   select 102 num from dual union all
   select 851 num from dual union all
   select 9856 num from dual union all
   select 356 num from dual union all
   select 748 num from dual union all
   select 254 num from dual
)
select n.num
     , substr(
          replace(replace(replace(replace(replace(replace(replace(replace(replace(replace(
             to_char(n.num,'TM9')
           , '0', ' zero' )
           , '1', ' one'  )
           , '2', ' two'  )
           , '3', ' three')
           , '4', ' four' )
           , '5', ' five' )
           , '6', ' six'  )
           , '7', ' seven')
           , '8', ' eight')
           , '9', ' nine' )
        , 2
       ) as string
  from n
;


Since we only need to handle each digit separately (and not write "forty-two hundred and sixty-nine"), replacing each "string digit" with it's "word equivalent" will do. SUBSTR just gets rid of the leading space.

Whether it is more performant to do a series of REPLACE compared to creating rows per digit and aggregating back, that is a question I have not tested ;-)

Cheerio
/Kim

Connor McDonald

Followup  

December 14, 2017 - 7:52 am UTC

nice work

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.