List:General Discussion« Previous MessageNext Message »
From:Tim Legg Date:November 5 2009 9:35pm
Subject:Removing 1st character of string for all entries in field
View as plain text  
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


      
Thread
Removing 1st character of string for all entries in fieldTim Legg5 Nov
  • Re: Removing 1st character of string for all entries in fieldKyong Kim5 Nov
    • Re: Removing 1st character of string for all entries in fieldTim Legg5 Nov
      • Re: Removing 1st character of string for all entries in fieldKyong Kim6 Nov