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 <kykimdba@stripped> wrote:
> From: Kyong Kim <kykimdba@stripped>
> Subject: Re: Removing 1st character of string for all entries in field
> To: "Tim Legg" <kc0ofc@stripped>
> Cc: mysql@stripped
> 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 <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
> >
> >
>
