funky characters in columns

2007-10-01 Thread Jay Blanchard 
I did some googleing and some other searching, now I am looking for a
cure all. I have a column into which it appears that a carriage return
has been inserted and it is mucking about with some queries;

mysql select dealerLong from profile where id = '130';
++
| dealerLong |
++
   |.9040
++

(the number contained therein should be 98.9040). I know that the column
should be set up as a float, but this is an older database and was not
set up that waymine left to correct.

For troubleshooting purposes, once I had narrowed down the problem
column I did the following

mysql select concat('|', dealerLong, '|') from profile where id =
'130';
+--+
| concat('|', dealerLong, '|') |
+--+
|   |
+--+

You will note the way that the column displays, appearing to have no
data at all. This is typically caused by having a carriage return
somewhere in the column.

update profile set dealerLong = replace(dealerLong, char(13), ) where
id = '130';

has no affect. So I need to see all of the characters inn the column so
that I can determine how to replace.

Can someone point me in the correct direction? I sure do appreciate any
help that you can give me. I certainly do not want to have to go through
each record that is borked up separately.



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: funky characters in columns

2007-10-01 Thread Baron Schwartz 

Hi Jay,

Try this:

http://www.xaprb.com/blog/2006/04/14/bad-character-data-in-mysql/

Baron

Jay Blanchard wrote:

I did some googleing and some other searching, now I am looking for a
cure all. I have a column into which it appears that a carriage return
has been inserted and it is mucking about with some queries;

mysql select dealerLong from profile where id = '130';
++
| dealerLong |
++
   |.9040
++

(the number contained therein should be 98.9040). I know that the column
should be set up as a float, but this is an older database and was not
set up that waymine left to correct.

For troubleshooting purposes, once I had narrowed down the problem
column I did the following

mysql select concat('|', dealerLong, '|') from profile where id =
'130';
+--+
| concat('|', dealerLong, '|') |
+--+
|   |
+--+

You will note the way that the column displays, appearing to have no
data at all. This is typically caused by having a carriage return
somewhere in the column.

update profile set dealerLong = replace(dealerLong, char(13), ) where
id = '130';

has no affect. So I need to see all of the characters inn the column so
that I can determine how to replace.

Can someone point me in the correct direction? I sure do appreciate any
help that you can give me. I certainly do not want to have to go through
each record that is borked up separately.





--
Baron Schwartz
Xaprb LLC
http://www.xaprb.com/

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: funky characters in columns

2007-10-01 Thread Gary Josack 

Try:

replace(replace(dealerLong, '\n', ''), '\r', '')

Jay Blanchard wrote:

I did some googleing and some other searching, now I am looking for a
cure all. I have a column into which it appears that a carriage return
has been inserted and it is mucking about with some queries;

mysql select dealerLong from profile where id = '130';
++
| dealerLong |
++
   |.9040
++

(the number contained therein should be 98.9040). I know that the column
should be set up as a float, but this is an older database and was not
set up that waymine left to correct.

For troubleshooting purposes, once I had narrowed down the problem
column I did the following

mysql select concat('|', dealerLong, '|') from profile where id =
'130';
+--+
| concat('|', dealerLong, '|') |
+--+
|   |
+--+

You will note the way that the column displays, appearing to have no
data at all. This is typically caused by having a carriage return
somewhere in the column.

update profile set dealerLong = replace(dealerLong, char(13), ) where
id = '130';

has no affect. So I need to see all of the characters inn the column so
that I can determine how to replace.

Can someone point me in the correct direction? I sure do appreciate any
help that you can give me. I certainly do not want to have to go through
each record that is borked up separately.



  



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: funky characters in columns

2007-10-01 Thread Jay Blanchard 
[snip]
Try:

replace(replace(dealerLong, '\n', ''), '\r', '')
[/snip]

Didn't work, perhaps because they are hidden. I ended up taking the long
road;

update table set foo = replace(HEX(foo), '0D', '');
update table set foo = UNHEX(foo);

HEX allowed me to see the carriage return (0D) and then use replace
syntax to fix.



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]