Converting decimal numbers to Words

Hi there all the Gurus,
I am trying to mave a convert a decimal number ie. 1230.30 to words in my report and i am getting result with the 30/100 at the back of the words.
Is there any way i can get a result of :
'ONE THOUSAND TWO HUNDRED THIRTY AND THIRTY'Well, I guess you could split the number into two parts (at the decimal point) and convert each separately.|||UpperCase(TOWORDS(integer({INVOICE.AMOUNT}))) + Uppercase(TOWORDS(fraction({INVOICE.AMOUNT}))) + 'CENTS ONLY'

i have tried this formula but it keeps prompting error "Missing '('

Please advice|||I was thinking something more like:

local stringvar sNumber := cstr(12345.67);
local numbervar whole := truncate(12345.67);
local numbervar pos := instrrev(sNumber, '.');
local numbervar decimal := 0;

if pos > 0 then
(
decimal := CDbl(right(sNumber, length(sNumber) - pos));
);

Uppercase(ToWords(whole, 0) + ' Dollars and ' + ToWords(decimal, 0) + ' Cents Only');|||Cool! Many thanks for the solution.|||I was thinking something more like:

local stringvar sNumber := cstr(12345.67);
local numbervar whole := truncate(12345.67);
local numbervar pos := instrrev(sNumber, '.');
local numbervar decimal := 0;

if pos > 0 then
(
decimal := CDbl(right(sNumber, length(sNumber) - pos));
);

Uppercase(ToWords(whole, 0) + ' Dollars and ' + ToWords(decimal, 0) + ' Cents Only');

Hi, if I want to convert a Sum of Amount into English Words? How to do this?|||I was thinking something more like:

local stringvar sNumber := cstr(12345.67);
local numbervar whole := truncate(12345.67);
local numbervar pos := instrrev(sNumber, '.');
local numbervar decimal := 0;

if pos > 0 then
(
decimal := CDbl(right(sNumber, length(sNumber) - pos));
);

Uppercase(ToWords(whole, 0) + ' Dollars and ' + ToWords(decimal, 0) + ' Cents Only');

Hi there,

If i am not wrong, you must create a formula or using the SUM maths function to get the total amount then put it replacing the (12345.67)

or we shall hear what's the Guru says :)|||Hi there,

If i am not wrong, you must create a formula or using the SUM maths function to get the total amount then put it replacing the (12345.67)

or we shall hear what's the Guru says :)

Yap, I have already tried in this way, which replace the 12345.67 into sum(@.amount), yet it doesn't work.

local stringvar sNumber := cstr(sum({@.Amount}));
local numbervar whole := truncate(sum({@.Amount}));
local numbervar pos := instrrev(sNumber, '.');

local numbervar decimal := 0;
if pos > 0 then
(
decimal := CDbl(right(sNumber, length(sNumber) - pos));
);

Uppercase(ToWords(whole, 0) + ' Ringgit and ' + ToWords(decimal, 0) + ' Sen Sahaja');|||What error do you get? Presumably 'This field cannot be summarised' when you run the report as you can't sum a formula {@.amount}.
Try summing something that can be summed like the underlying database value, or maybe {@.amount} is already your summed value?sqlsql

Convert Punctuation to Spaces?

Hi,

I have a table of text. I need to search for whole words within this text...
For example, I need to be able to search for records that contain 'dog' but
not return 'hotdog' or 'dogma' for example.

I am doing this by throwing a space around both the records in the table and
the search word like this:
WHERE (' ' + Text + ' ') Like ('% ' + Search + ' %')

The problem is that punctuation needs to be stripped out of the text so that
it will still find "...walking the dog."

Is there a way to update, converting a certain set of characters into
another character (i.e. a space) and/or to do the same thing during the word
search query itself?

Thanks!"HumanJHawkins" <JHawkins@.HumanitiesSoftware.Com> wrote in message
news:rlmbc.13192$lt2.8227@.newsread1.news.pas.earth link.net...
> Hi,
> I have a table of text. I need to search for whole words within this
text...
> For example, I need to be able to search for records that contain 'dog'
but
> not return 'hotdog' or 'dogma' for example.
> I am doing this by throwing a space around both the records in the table
and
> the search word like this:
> WHERE (' ' + Text + ' ') Like ('% ' + Search + ' %')
> The problem is that punctuation needs to be stripped out of the text so
that
> it will still find "...walking the dog."
> Is there a way to update, converting a certain set of characters into
> another character (i.e. a space) and/or to do the same thing during the
word
> search query itself?
> Thanks!

Assuming you have MSSQL 2000, you could write a UDF to remove all
punctuation characters from a string, but then you'd end up with this:

WHERE dbo.fn_RemovePunc(MyColumn) LIKE '% ' + @.SearchString + ' % '

That will probably cause a performance issue, because the UDF will be
invoked once per row during queries, although you could create a computed
column using the UDF and index it.

However, perhaps a better solution here would be to look at using full-text
indexing? The CONTAINS() predicate can do what you need, and is much more
powerful than LIKE.

Simon|||"Simon Hayes" <sql@.hayes.ch> wrote in message
news:406e7002$1_1@.news.bluewin.ch...
> "HumanJHawkins" <JHawkins@.HumanitiesSoftware.Com> wrote in message
> news:rlmbc.13192$lt2.8227@.newsread1.news.pas.earth link.net...
>> <CUT>For example, I need to be able to search for records that contain
'dog'
>> but not return 'hotdog' or 'dogma' for example.
>> <CUT
> The CONTAINS() predicate can do what you need, and is much more
> powerful than LIKE.

That helped tons. I got the basic "CONTAINS" predicate to work, but do not
get any results when I add "FORMSOF" into the mix. Do you see the problem
with the following?

WHERE CONTAINS (vchContentText , ' FORMSOF (INFLECTIONAL,
@.SearchIncludes) ')

All of the examples I found seemed to have a space and single quotes around
the whole "FORMSOF" bit, though it didn't seem to matter whether I removed
the space or the single quotes.

Thanks!|||"HumanJHawkins" <JHawkins@.HumanitiesSoftware.Com> wrote in message
news:dtjcc.16960$lt2.8344@.newsread1.news.pas.earth link.net...
> "Simon Hayes" <sql@.hayes.ch> wrote in message
> news:406e7002$1_1@.news.bluewin.ch...
> > "HumanJHawkins" <JHawkins@.HumanitiesSoftware.Com> wrote in message
> > news:rlmbc.13192$lt2.8227@.newsread1.news.pas.earth link.net...
> >> <CUT>For example, I need to be able to search for records that contain
> 'dog'
> >> but not return 'hotdog' or 'dogma' for example.
> >> <CUT>
> > The CONTAINS() predicate can do what you need, and is much more
> > powerful than LIKE.
> That helped tons. I got the basic "CONTAINS" predicate to work, but do not
> get any results when I add "FORMSOF" into the mix. Do you see the problem
> with the following?
> WHERE CONTAINS (vchContentText , ' FORMSOF (INFLECTIONAL,
> @.SearchIncludes) ')
> All of the examples I found seemed to have a space and single quotes
around
> the whole "FORMSOF" bit, though it didn't seem to matter whether I removed
> the space or the single quotes.
> Thanks!

This may help:

http://oldlook.experts-exchange.com...Q_20711909.html

Fulltext is quite a specialized area, and it seems to have a number of
quirks, so you may want to consider posting questions in
microsoft.public.sqlserver.fulltext - you'll probably get a better response.

Simon|||> This may help:
>
http://oldlook.experts-exchange.com...Q_20711909.html
> Fulltext is quite a specialized area, and it seems to have a number of
> quirks, so you may want to consider posting questions in
> microsoft.public.sqlserver.fulltext - you'll probably get a better
response.

Thanks!|||>"HumanJHawkins" <JHawkins@.HumanitiesSoftware.Com> wrote in message
> news:rlmbc.13192$lt2.8227@.newsread1.news.pas.earth link.net...
>><CUT>I need to be able to search for records that contain 'dog'
>> but not return 'hotdog' or 'dogma' for example.
>> <CUT
"Simon Hayes" <sql@.hayes.ch> replied in message
news:406e7002$1_1@.news.bluewin.ch...
>perhaps a better solution here would be to look at using full-text
> indexing? The CONTAINS() predicate can do what you need, and is much more
> powerful than LIKE.

Thanks Simon. The syntax needed is:

In SQL:
-- In the declarations or parameters:
@.Variable varchar(256) = 'FORMSOF(INFLECTIONAL,"word")'

-- Then, in the WHERE clause:
CONTAINS (TableName, @.Variable)

If passing the string from VB to a stored procedure, prepare the string in
VB with:
TheVariable= "'FORMSOF(INFLECTIONAL,""" & TheVariable & """)'"

Cheers!!

convert keywords to UpperCase

I don't know if this is in the correct forum, but is there a simple utility to convert all reserved words to upper case in SQL Server 2005?

The find and replace is not the best option as there could be some field values/comments that would get converted.

With > 100 stored procedures/views something like this would be very beneficial. :)

Thanks
I don′t know of any tool like this, you could sure write one on your own if you have the bunch of reserverd words. But pro-actively you could use some other tool which keeps track of writing the keyword upper case while you are typing, like SQLPrompt which is free now:

http://www.sqlserver2005.de/SQLServer2005/MyBlog/tabid/56/EntryID/8/Default.aspx

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

Convert Int Values into words..?

Hi,

I'm doing an online invoice system with ASP and MS SQL.

So you got the price and total amount.

And let say the amount is $4000.00 , there is also a portion where the invoice needs to mention it in words, "Fourty Thousand"

Can it be "cast" ? "conver" ? or anything?
Or is there any workaround??$4000.00 = "Fourty Thousand"

I better should be careful while doing any transactions with you ;).

Maybe a function can be written to get it done ... will try it out ...

Highly unlikely you will get a readymade function for the same ...|||Opps..sorry...I was kindy sleepy..:)

thanks...I'll be trying out some code too...so many numbers condition to check in the if then else....|||don't write your own, there are so many scripts out there already, i'm sure you can adapt the logic, even if they aren't all written in wahtever language you plan to use...

http://developer.asna.com/Articles/b000106.asp
http://www.idinews.com/sourcecode/IntegerFunction.html
http://www.mvps.org/access/modules/mdl0001.htm
http://www.vbce.com/code/functions/convert_numbers_to_words/index.asp
http://www.vbexplorer.com/VBExplorer/tips/src36.asp

how did i find these so quickly?

google

Convert Digit To Word (Indian Format)

in crystal repot how to convert a digit to words(indian formate)
ex:
if the digit is 10 then "ten"
if the digit is 100000 then "one lakh"( this is i just tell indian formate)
plz send me the answ:as soon as u possibleAre you using Visual Basic
If so, which version

AFAIK, Crystal Reports cannot do this on its own|||in crystal repot how to convert a digit to words(indian formate)
ex:
if the digit is 10 then "ten"
if the digit is 100000 then "one lakh"( this is i just tell indian formate)

plz send me the answ:as soon as u possible

yes dear it is possible with the in build function i.e towords() this is used to convert the digit to word. just create new formula write this function in it and you just have to pass the field containing digit.

For e.g table name is invoice and field name is invoice_amount and you wants to convert it in to digit you can use like this in formula editor

towords({invoice.invoice_amount})

this will convert digit to word.

enjoy and if have any other doubt please do ask me or email me at nkmomin2006@.yahoo.com