Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Akshay.

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

Last updated: December 14, 2017 - 7:52 am UTC

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

Rating

  (1 rating)

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

Comments

An alternative without string aggregation

Kim Berg Hansen, December 13, 2017 - 5:14 pm UTC

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
December 14, 2017 - 7:52 am UTC

nice work

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.