List:General Discussion« Previous MessageNext Message »
From:Kyong Kim Date:November 6 2009 12:14am
Subject:Re: Removing 1st character of string for all entries in field
View as plain text  
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 <kc0ofc@stripped> 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 <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
>> >
>> >
>>
>
>
>
>
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