Re: Cleaning muck out of data fields
Hi,
update tbl_products set p10_price=mid(p10_price,2) where p10_price regexp
('[^0-9.]');
Which worked because the mucky characters were always the first two digits but
it's still cludgy.
What I really wanted to do was just "filter out the good any currency numerics of form \d+.\d\d
10.95 but as mysql only supports regexp when matching,
I reckon a regex_replace would be a great idea... any plan for that
feature ?
--
Philippe Poelvoorde
COS Trading Ltd.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Cleaning muck out of data fields
On Fri, 13 May 2005 08:29:46 +0200, wrote: >Hi, >if the first characters are numerics, no need to use regexp, since mysql does >implicit conversion if you do calculations : > >mysql> select '10.95 tiitti' from dual; >+--+ >| 10.95 tiitti | >+--+ >| 10.95 tiitti | >+--+ >1 row in set (0.09 sec) > >mysql> select '10.95 tiitti'+0 from dual; >+--+ >| '10.95 tiitti'+0 | >+--+ >|10.95 | >+--+ >1 row in set (0.02 sec) > Mathais Can I write the clean value back? , (i will give it a try) -- zzapper vim -c ":%s%s*%Cyrnfr)fcbafbe[Oenz(Zbbyranne%|:%s)[[()])-)Ig|norm Vg?" http://www.rayninfo.co.uk/tips/ vim, zsh & success tips -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Cleaning muck out of data fields
Hi,
if the first characters are numerics, no need to use regexp, since mysql does
implicit conversion if you do calculations :
mysql> select '10.95 tiitti' from dual;
+--+
| 10.95 tiitti |
+--+
| 10.95 tiitti |
+--+
1 row in set (0.09 sec)
mysql> select '10.95 tiitti'+0 from dual;
+--+
| '10.95 tiitti'+0 |
+--+
|10.95 |
+--+
1 row in set (0.02 sec)
Mathias
Selon zzapper <[EMAIL PROTECTED]>:
> Hi,
>
> I wanted to clean up some numeric currency data fields which had some
> non-numeric values which took
> the first two characters of the field (they were some kind of garbage
> characters) anyway the
> following did the trick
>
> update tbl_products set p10_price=mid(p10_price,2) where p10_price regexp
> ('[^0-9.]');
>
> Which worked because the mucky characters were always the first two digits
> but it's still cludgy.
>
> What I really wanted to do was just "filter out the good any currency
> numerics of form \d+.\d\d
> 10.95 but as mysql only supports regexp when matching, I couldn't think of a
> way. I have the same
> problem if I try to "Locate" I cant AFAIK say locate first digit.
>
> Comments/Ideas?
>
> Q2)
> Can I match mucky non-alphanumerics ?
> --
> zzapper
> vim -c ":%s%s*%Cyrnfr)fcbafbe[Oenz(Zbbyranne%|:%s)[[()])-)Ig|norm Vg?"
> http://www.rayninfo.co.uk/tips/ vim, zsh & success tips
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
>
>
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Cleaning muck out of data fields
Hi,
I wanted to clean up some numeric currency data fields which had some
non-numeric values which took
the first two characters of the field (they were some kind of garbage
characters) anyway the
following did the trick
update tbl_products set p10_price=mid(p10_price,2) where p10_price regexp
('[^0-9.]');
Which worked because the mucky characters were always the first two digits but
it's still cludgy.
What I really wanted to do was just "filter out the good any currency numerics
of form \d+.\d\d
10.95 but as mysql only supports regexp when matching, I couldn't think of a
way. I have the same
problem if I try to "Locate" I cant AFAIK say locate first digit.
Comments/Ideas?
Q2)
Can I match mucky non-alphanumerics ?
--
zzapper
vim -c ":%s%s*%Cyrnfr)fcbafbe[Oenz(Zbbyranne%|:%s)[[()])-)Ig|norm Vg?"
http://www.rayninfo.co.uk/tips/ vim, zsh & success tips
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
