From: Gary Josack (garybyoteki.com)
Date: Mon Oct 01 2007 - 17:37:18 CDT

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]

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.
>
>
>
>

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]