I think you can use update replace.
UPDATE table SET column=REPLACE(column,'$','');
Kyong
On Thu, Nov 5, 2009 at 1:35 PM, Tim Legg <kc0ofc@stripped> wrote:
> Hello,
>
> I am importing CSV data from a proprietary database into a table in MySQL. Due
> to a flaw in the proprietary software's export tool, currency values (floats) are always
> exported with a '$' prefixed to them. This causes a problem where the matching float
> field in the MySQL table being set to zero (or not set at all) after import.
>
> As a solution to getting a complete import, I modified the data type of the field in
> MySQL from float to varchar(8), so now the data is present in the table.
>
> I am faced with the problem of removing the '$' from the string.
>
> I can filter out the '$' by doing a string manipulation,
>
> SELECT MID((SELECT `imported_data`.`PartPrice` FROM `imported_data` WHERE
> `imported_data`.`PartNumber`='1') FROM 2);
>
> I can change the value of a record from $100 to 100 by hand.
>
> UPDATE `imported_data` SET `imported_data`.`PartPrice`='100' WHERE
> `imported_data`.`ParttNumber`='49152';
>
> And thus tried,
>
> UPDATE `imported_data` SET `imported_data`.`PartPrice`=(SELECT MID((SELECT
> `imported_data`.`PartPrice` FROM `imported_data` WHERE
> `imported_data`.`PartNumber`='49152') FROM 2);) WHERE
> `imported_data`.`PartNumber`='49152';
>
> It was a nice try, but found out from MySQL that "You can't specify target table
> 'imported_data' for update in FROM clause" and discovered that it really looks like that I
> cannot write data to a table while a nested query is reading the same location.
>
> I could create a new field and insert into that instead of updating.
>
> But, regardless of the approach, I would still have to execute this statement some
> 8,000 times. Once for each part number I have. Putting something like this in
> a for-loop almost feels like I am avoiding a feature of convenience that I am not aware
> of.
>
> There really must be an easier way. Can anybody help me with a more elegant
> solution? (BTW, I have been explicitly forbidden from doing a search and replace on
> '$' with the CSV file that got exported)
>
>
> Thank you for assisting me and your support of a fine database software package!
>
>
> Timothy Legg
>
>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=1
>
>
