List:General Discussion« Previous MessageNext Message »
From:Gary Josack Date:October 1 2007 10:37pm
Subject:Re: funky characters in columns
View as plain text  
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 way....mine 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.
>
>
>
>   

Thread
funky characters in columnsJay Blanchard1 Oct
  • Re: funky characters in columnsBaron Schwartz1 Oct
  • Mysql innodb commandline check and repairBryan Cantwell1 Oct
    • Re: Mysql innodb commandline check and repairBaron Schwartz1 Oct
  • Re: funky characters in columnsGary Josack2 Oct
    • RE: funky characters in columnsJay Blanchard2 Oct