Yeah. Sometimes the manual is lacking in practical examples. Always good to try the list instead of just getting bogged down in the manual. Kyong
On Thu, Nov 5, 2009 at 2:28 PM, Tim Legg <kc0...@yahoo.com> wrote: > 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
