Holy Crapoly! SOLVED in 17 minutes!!!! That is a new record for me.
UPDATE `imported_data` SET `PartPrice`=REPLACE(`PartPrice`,'$',''); ...accomplished my task. I read the REPLACE page in section 12.2.7 of the online Reference Manual and didn't see the utility of it. The REPLACE page there is so unclear, I can't even backwards-comprehend the code using the documentation. Where on earth did you learn to code like this? A one-liner at that, even on an 80-column terminal. Thank you very much! Tim Legg --- On Thu, 11/5/09, Kyong Kim <kykim...@gmail.com> wrote: > From: Kyong Kim <kykim...@gmail.com> > Subject: Re: Removing 1st character of string for all entries in field > To: "Tim Legg" <kc0...@yahoo.com> > Cc: mysql@lists.mysql.com > Date: Thursday, November 5, 2009, 3:52 PM > 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 <kc0...@yahoo.com> > 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=kykim...@gmail.com > > > > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
