Removing 1st character of string for all entries in field

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=gcdmg-mysql-2@m.gmane.org

Re: Removing 1st character of string for all entries in field

I think you can use update replace.
UPDATE table SET column=3DREPLACE(column,'$','');

Kyong

On Thu, Nov 5, 2009 at 1:35 PM, Tim Legg wrote:
> Hello,
>
> I am importing CSV data from a proprietary database into a table in MySQL=
.. =A0Due to a flaw in the proprietary software's export tool, currency valu=
es (floats) are always exported with a '$' prefixed to them. =A0This causes=
a problem where the matching float field in the MySQL table being set to z=
ero (or not set at all) after import.
>
> As a solution to getting a complete import, I modified the data type of t=
he field in MySQL from float to varchar(8), so now the data is present in t=
he 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`=3D'1') FROM 2);
>
> I can change the value of a record from $100 to 100 by hand.
>
> UPDATE `imported_data` SET `imported_data`.`PartPrice`=3D'100' WHERE `imp=
orted_data`.`ParttNumber`=3D'49152';
>
> And thus tried,
>
> UPDATE `imported_data` SET `imported_data`.`PartPrice`=3D(SELECT MID((SEL=
ECT `imported_data`.`PartPrice` FROM `imported_data` WHERE `imported_data`.=
`PartNumber`=3D'49152') FROM 2);) WHERE `imported_data`.`PartNumber`=3D'491=
52';
>
> It was a nice try, but found out from MySQL that "You can't specify targe=
t table 'imported_data' for update in FROM clause" and discovered that it r=
eally looks like that I cannot write data to a table while a nested query i=
s 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 state=
ment some 8,000 times. =A0Once for each part number I have. =A0Putting some=
thing 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. =A0Can anybody help me with a more el=
egant solution? =A0(BTW, I have been explicitly forbidden from doing a sear=
ch and replace on '$' with the CSV file that got exported)
>
>
> Thank you for assisting me and your support of a fine database software p=
ackage!
>
>
> Timothy Legg
>
>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: =A0 =A0http://lists.mysql.com/mysql?unsub=3Dkykimdba@gmai=
l.com
>
>

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

Re: Removing 1st character of string for all entries in field

Holy Crapoly!

SOLVED in 17 minutes!!!! That is a new record for me.

UPDATE `imported_data` SET `PartPrice`=3DREPLACE(`PartPrice`,'$','');

....accomplished my task.

I read the REPLACE page in section 12.2.7 of the online Reference Manual an=
d didn't see the utility of it. The REPLACE page there is so unclear, I ca=
n't even backwards-comprehend the code using the documentation. Where on e=
arth did you learn to code like this? A one-liner at that, even on an 80-c=
olumn terminal.

Thank you very much!

Tim Legg
--- On Thu, 11/5/09, Kyong Kim wrote:

> From: Kyong Kim
> Subject: Re: Removing 1st character of string for all entries in field
> To: "Tim Legg"
> Cc: mysql@lists.mysql.com
> Date: Thursday, November 5, 2009, 3:52 PM
> I think you can use update replace.
> UPDATE table SET column=3DREPLACE(column,'$','');
>=20
> Kyong
>=20
> On Thu, Nov 5, 2009 at 1:35 PM, Tim Legg
> wrote:
> > Hello,
> >
> > I am importing CSV data from a proprietary database
> into a table in MySQL. =A0Due to a flaw in the proprietary
> software's export tool, currency values (floats) are always
> exported with a '$' prefixed to them. =A0This 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`=3D'1') FROM
> 2);
> >
> > I can change the value of a record from $100 to 100 by
> hand.
> >
> > UPDATE `imported_data` SET
> `imported_data`.`PartPrice`=3D'100' WHERE
> `imported_data`.`ParttNumber`=3D'49152';
> >
> > And thus tried,
> >
> > UPDATE `imported_data` SET
> `imported_data`.`PartPrice`=3D(SELECT MID((SELECT
> `imported_data`.`PartPrice` FROM `imported_data` WHERE
> `imported_data`.`PartNumber`=3D'49152') FROM 2);) WHERE
> `imported_data`.`PartNumber`=3D'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. =A0Once for each
> part number I have. =A0Putting 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. =A0Can anybody help
> me with a more elegant solution? =A0(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: =A0 =A0http://lists.mysql.com/mysql?unsub=3Dkykimdba@gm=
ail.com
> >
> >
> =0A

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

Re: Removing 1st character of string for all entries in field

Yeah. Sometimes the manual is lacking in practical examples.
Always good to try the list instead of just getting bogged down in the manu=
al.
Kyong

On Thu, Nov 5, 2009 at 2:28 PM, Tim Legg wrote:
> Holy Crapoly!
>
> SOLVED in 17 minutes!!!! =A0That is a new record for me.
>
> UPDATE `imported_data` SET `PartPrice`=3DREPLACE(`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. =A0The REPLACE page there is so unclear, =
I can't even backwards-comprehend the code using the documentation. =A0Wher=
e on earth did you learn to code like this? =A0A one-liner at that, even on=
an 80-column terminal.
>
> Thank you very much!
>
> Tim Legg
> --- On Thu, 11/5/09, Kyong Kim wrote:
>
>> From: Kyong Kim
>> Subject: Re: Removing 1st character of string for all entries in field
>> To: "Tim Legg"
>> Cc: mysql@lists.mysql.com
>> Date: Thursday, November 5, 2009, 3:52 PM
>> I think you can use update replace.
>> UPDATE table SET column=3DREPLACE(column,'$','');
>>
>> Kyong
>>
>> On Thu, Nov 5, 2009 at 1:35 PM, Tim Legg
>> wrote:
>> > Hello,
>> >
>> > I am importing CSV data from a proprietary database
>> into a table in MySQL. =A0Due to a flaw in the proprietary
>> software's export tool, currency values (floats) are always
>> exported with a '$' prefixed to them. =A0This 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`=3D'1') FROM
>> 2);
>> >
>> > I can change the value of a record from $100 to 100 by
>> hand.
>> >
>> > UPDATE `imported_data` SET
>> `imported_data`.`PartPrice`=3D'100' WHERE
>> `imported_data`.`ParttNumber`=3D'49152';
>> >
>> > And thus tried,
>> >
>> > UPDATE `imported_data` SET
>> `imported_data`.`PartPrice`=3D(SELECT MID((SELECT
>> `imported_data`.`PartPrice` FROM `imported_data` WHERE
>> `imported_data`.`PartNumber`=3D'49152') FROM 2);) WHERE
>> `imported_data`.`PartNumber`=3D'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. =A0Once for each
>> part number I have. =A0Putting 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. =A0Can anybody help
>> me with a more elegant solution? =A0(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: =A0 =A0http://lists.mysql.com/mysql?unsub=3Dkykimdba@g=
mail.com
>> >
>> >
>>
>
>
>
>

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg