Stripping out specific characters

[pixiesfb]

I have a column in a database with data like this:

$22.25
$443.93
$2.50

I would like to run a query to strip the dollar signs to get this:

22.25
443.93
2.50

Thanks!

 

[chandler]

use the numberformat() function. something like numberformat(FIELD,"00.00&quot
Chandler
I ran over my dogma with karma!

 

[mikeshort]

A more general approach will also work. The Replace function can be used to replace any substring in a string with any other substring. In our case, you would replace the '$' with the null string:

<cfset newValue = Replace( value, &quot;$&quot;, &quot;&quot;, &quot;ALL&quot; )>

You don't need the "ALL" since you know that there is exactly one occurrence of the dollar sign.

Mike

 

[CFHub]

Replace(YourColumn,'$','','all') would do it in your CFML template output although if you can be sure that all your columns have a precedding '$' you could try doing it right in the SQL.

SELECT
Right(YourColumn,(len(YourColumn) - 1)) AS NoDollars

Scalar functions can be much faster than thier CFML counterparts.

http://cfhub.com/SQL/scalars/index.cfm

 

[calista]

Maybe I'm overlooking something, here, but is there any particular reason you have to store the number with a $? Why not just store it in the database as a decimal number and use the DollarFormat function when you want to display it? Calista :-X
Jedi Knight,
Champion of the Force