How to spell out numeric value in English words by select query.

Database

How to spell out numeric value in English words by select query.

Hi ALL,

Can any one help me how to use Element (SP) in To_char function to spell out the number in english words?

I am using TD 14 verion>

Below content mentioned in TD14 doc about SP:

==================================

Spelled. Any numeric element followed by SP is

spelled in English words. The words are

capitalized according to how the element is

capitalized.

For example: 'DDDSP' specifies all uppercase,

'DddSP' specifies that the first letter is

capitalized, and 'dddSP' specifies all lowercase.

Note: SP is only supported by the TO_CHAR

function.

12 REPLIES

Re: How to spell out numeric value in English words by select query.

Hi All,
Can any one help me on this?

Thanks
sai
N/A

Re: How to spell out numeric value in English words by select query.

Hi Sai,

"sp" in Oracle is only supported for DateTime, same in Teradata:

SELECT TO_CHAR(CURRENT_DATE,'yyyysp');

*** Query completed. One row found. One column returned.
*** Total elapsed time was 1 second.

TO_CHAR(Date,'yyyysp')
-----------------------------------------------------------------
two thousand thirteen

BTEQ -- Enter your SQL request or BTEQ command:
SELECT CAST(TO_CHAR(CURRENT_DATE,'jSp') AS VARCHAR(100));

*** Query completed. One row found. One column returned.
*** Total elapsed time was 1 second.

TO_CHAR(Date,'jSp')
---------------------------------------------------------------------
two million four hundred fifty-six thousand four hundred eighty-six

Of course you could do some int-to-date calculations to get it for numbers, too.

How many digits do you need?

Dieter

Dieter

Re: How to spell out numeric value in English words by select query.

Hi  Dnoeth,

THnaks for the information.It is really usefull for me.

I have one more question.

Can't we spellout the numbers in terdata?

In requirement have a column having 4 degit numbers  and need to spellout it at report level.

Could you please help on this? and also let me know any limitations on interger(like range..etc)

Thanks ,

Sai

Re: How to spell out numeric value in English words by select query.

Hi Dnoeth,

THnaks for the information.It is really usefull for me.

I have one more question.

Can't we spellout the numbers in terdata?

In requirement have a column having 4 degit numbers and need to spellout it at report level.

Could you please help on this? and also let me know any limitations on interger(like range..etc)

In Oracle "

SQL> select sal, (to_char(to_date(sal,'j'), 'jsp'))
from emp;
the output like,
SAL (TO_CHAR(TO_DATE(SAL,'J'),'JSP'))
--------- -----------------------------------------------------
800 eight hundred
1600 one thousand six hundred
1250 one thousand two hundred fifty
If you want to add some text like,
Rs. Three Thousand only.
SQL> select sal "Salary ",
(' Rs. '|| (to_char(to_date(sal,'j'), 'Jsp'))|| ' only.'))
"Sal in Words" from emp
/
Salary Sal in Words
------- ------------------------------------------------------
800 Rs. Eight Hundred only.
1600 Rs. One Thousand Six Hundred only.
1250 Rs. One Thousand Two Hundred Fifty only.
14. Display Odd/ Even number of records

How to achive this in terdata?

Thanks ,
Sai
N/A

Re: How to spell out numeric value in English words by select query.

Hi Sai,

if it's between 1 and 9999:

TO_CHAR((x-1900)*10000+101 (DATE) ,'yyyysp')


Dieter

Re: How to spell out numeric value in English words by select query.

Thanks a lot Dieter.

Re: How to spell out numeric value in English words by select query.

So we can't spellout beyond 9999
N/A

Re: How to spell out numeric value in English words by select query.

Re: How to spell out numeric value in English words by select query.

Hi Dieter@could you please share your mail id