Home » SQL & PL/SQL » SQL & PL/SQL » NUMERIC VALUES IN WORDS
NUMERIC VALUES IN WORDS [message #183896] Mon, 24 July 2006 05:09 Go to next message
guru_karnam
Messages: 142
Registered: May 2005
Senior Member
hello,

when we say

select sal,to_char(to_date(sal,'J'),'JSP') FROM emp
we get the o/p as follows:

SAL TO_CHAR(TO_DATE(SAL,'J'),'JSP')
--------- ------------------------------------------------
800 EIGHT HUNDRED
1600 ONE THOUSAND SIX HUNDRED
1250 ONE THOUSAND TWO HUNDRED FIFTY
2975 TWO THOUSAND NINE HUNDRED SEVENTY-FIVE
1250 ONE THOUSAND TWO HUNDRED FIFTY
2850 TWO THOUSAND EIGHT HUNDRED FIFTY
2450 TWO THOUSAND FOUR HUNDRED FIFTY
3000 THREE THOUSAND
5000 FIVE THOUSAND
1500 ONE THOUSAND FIVE HUNDRED
1100 ONE THOUSAND ONE HUNDRED
950 NINE HUNDRED FIFTY
3000 THREE THOUSAND
1300 ONE THOUSAND THREE HUNDRED

But i want the words to be displayed as

for 1250 i want one thousand two hundred and fifty
for 2975 i want two thousand nine hundred and seventy five

Likewise "and " should be displayed whereever required.



Ravi
Re: NUMERIC VALUES IN WORDS [message #183904 is a reply to message #183896] Mon, 24 July 2006 05:32 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
What query did you use in order to fix it? Why didn't it work? How would you, theoretically speaking, solve this problem? What prevented you to apply theory to praxis?
Re: NUMERIC VALUES IN WORDS [message #183941 is a reply to message #183896] Mon, 24 July 2006 08:27 Go to previous messageGo to next message
joy_division
Messages: 4644
Registered: February 2005
Location: East Coast USA
Senior Member
raviraviki wrote on Mon, 24 July 2006 06:09


But i want the words to be displayed as

for 1250 i want one thousand two hundred and fifty
for 2975 i want two thousand nine hundred and seventy five

Likewise "and " should be displayed whereever required.



Ravi


But that is not the proper way to say, show or display a number. There is no such thing as "and" in any number.
Re: NUMERIC VALUES IN WORDS [message #183992 is a reply to message #183941] Mon, 24 July 2006 14:28 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8636
Registered: November 2002
Location: California, USA
Senior Member
joy_division wrote on Mon, 24 July 2006 06:27


But that is not the proper way to say, show or display a number. There is no such thing as "and" in any number.


It depends on where you are. In England, where English comes from, it is proper. In America, the AND is omitted. There are many differences between English and American that many are unaware of. American is often misrepresented as English. A classic example is the British movie, "One Hundred and One Dalmations." It is not one hundred one dalmations. There are many websites that discuss this. The following quote:

"The British use 'and' before tens and ones but the Americans usually leave the 'and' out."

comes from this one:

http://www.studyenglishtoday.net/cardinal-numbers.html

The jsp only converts to American, not English, although they may call it English. If you need to add the AND where you are, then you will need to write a wrapper function or something to do that.

[Updated on: Mon, 24 July 2006 14:30]

Report message to a moderator

Re: NUMERIC VALUES IN WORDS [message #183996 is a reply to message #183896] Mon, 24 July 2006 14:46 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8636
Registered: November 2002
Location: California, USA
Senior Member
If you click on the link below and scroll down to the review about UK English, Tom Kyte has posted a revised function to return numbers as words with AND where the British would use it.
http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:1407603857650
Re: NUMERIC VALUES IN WORDS [message #184000 is a reply to message #183992] Mon, 24 July 2006 15:07 Go to previous message
joy_division
Messages: 4644
Registered: February 2005
Location: East Coast USA
Senior Member
Thank you very much for enlightening me Barbara. You learn something new everyday.
Previous Topic: Synonym (Merged)
Next Topic: sub sub queries
Goto Forum:
  


Current Time: Sun Dec 11 00:07:53 CST 2016

Total time taken to generate the page: 0.04927 seconds