Stripping out characters 1

[Aeros]

I have a price field in my form that gets inserted into a money datatype field in my db. Problem is some times the person will enter a $ character:

19.99 (fine)
$19.99 (bad)

Before I process this to insert to the db whats the best way of stripping out non-integer characters?
Thanks

 

[Aeros]

Sorry I meant this datafield is an 'integer' datatype

 

[skobo]

REPLACE(variablename,"$","&quot

will strip out the first instance of the dollar sign character. Since not all people are putting in dollar signs, this is a good method since it won't change the value if the $ isn't there.

Chris

 

[Aeros]

Well this ends up converting it to a string and it needs to be an integer to be INSERTed into a field of integer datatype. Is there a way to convert the string to an integer?

 

[skobo]

Cold Fusion is not strongly typed, so the value you get after removing the dollar sign is not necessarily a string, unless you use it as one. You will find that if you use the IsNumeric function on the value, it can be used as a numeric value since it only contains numbers and a decimal point.

Your problem is that are attempting to insert a value with a decimal point into an integer field in your database. Integer values are whole numbers with no decimal part. You can't convert a decimal value into an integer without changing it's value. If you need the value to an integer you will either need to get rid of the decimal part using the ROUND(), CEILING(), FIX() or INT() function.

Or you could change the field in your database to a datatype that allows decimal values. It really depends on what you need your application to do.

Chris

 

[csteinhilber]

That, sir, was a lesson in "brief-and-to-the-point"!

Excellent post... full of useful info, answers the question succinctly, accurate, easy to read, and only 3 paragraphs long. I'll give you a star just because it's a thing of beauty.

I envy you. My posts seem to run on and on

Hope it helps,
-Carl