Help with SQL question

[gte806e]

I'm trying to change the datatype of an existing column from VARCHAR to double. This field was used to hold price and now I want to to be a number so I can search/sort it by numerical value.

So, The old system allowed the "$" to be included in the number, so before I change the datatype how can i remove all instances of the $ from the table?

Is there some sort of find and replace in sql?

Thanks

Jesse

[jayant]

UPDATE <table-name> SET <field-name> = SUBSTRING(<field-name>,2) WHERE <field-name> LIKE "$%";

Note: it is recommended that you take a backup before you run this query.

[gte806e]

Awsome thanks so much, I ended up writing a php script that did that as well, however, yours worked much better / faster.\

Could you reccomend a good SQL reference that would give me quick access to functions like SUBSTRING(). As I see it worked but not sure what it does lol. I tried looking it up on mysql.com and that site is very difficult to find anything on.

TIA

Jesse

[bubblenut]

here