All Forums Database
ksaikrishna 26 posts Joined 08/11
11 Jul 2013
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.
 

ksaikrishna 26 posts Joined 08/11
11 Jul 2013

Hi All,
Can any one help me on this?

Thanks
sai

dnoeth 4628 posts Joined 11/04
12 Jul 2013

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

Dieter

ksaikrishna 26 posts Joined 08/11
12 Jul 2013

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
 

ksaikrishna 26 posts Joined 08/11
12 Jul 2013

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

dnoeth 4628 posts Joined 11/04
12 Jul 2013

Hi Sai,
if it's between 1 and 9999:
 
TO_CHAR((x-1900)*10000+101 (DATE) ,'yyyysp')

 

Dieter

Dieter

ksaikrishna 26 posts Joined 08/11
12 Jul 2013

Thanks a lot Dieter.

ksaikrishna 26 posts Joined 08/11
12 Jul 2013

So we can't spellout beyond 9999

dnoeth 4628 posts Joined 11/04
12 Jul 2013

You can :-)
http://developer.teradata.com/node/182312
 
 
Dieter

Dieter

06 Oct 2013

Hi Dieter@could you please share your mail id 

dnoeth 4628 posts Joined 11/04
06 Oct 2013

When you go to my profile page you'll see my email.
Dieter

Dieter

06 Oct 2013

i am not able to find your mail ID in your profile page
 

06 Oct 2013

Please mail u r mail ID :nagesh.dwh123@gmail.com
 

You must sign in to leave a comment.