How to delete /r/n after using Navicat in phpmyadmin/Navicat?

I have converted a xml-file to mysql with Navicat but the problem with the
xml-file was that the end of line code wasn't changed before I used Navaicat
so I had to look for it manually and change it in there but in Firebug I
still saw this code \r\n.

I have been told that this \r\n is characters 13,10 (CR, LF) and is the
standard DOS sequence for end-of-line.

Now when I try to geocode the adresses from phpmyadmin I got problems and
now I need help to search for this code and delete it to my script can
geocode the adresses without problem. I could do it manually but the
database has about 600 post. Can someone help with the code to use, please?

It look like this in phpadmin [screencast.com] and it should looke like this
[screencast.com]

/Tine

Re: How to delete /r/n after using Navicat in phpmyadmin/Navicat?

I got the solution in another forum and it worked.:-)

update table_name

set columns_name = replace(column_name,"\r\n","")


"Tine Müller" skrev i en meddelelse
news:Pi4Ti.33$1P4.9@news.get2net.dk...
>I have converted a xml-file to mysql with Navicat but the problem with the
>xml-file was that the end of line code wasn't changed before I used
>Navaicat so I had to look for it manually and change it in there but in
>Firebug I still saw this code \r\n.
>
> I have been told that this \r\n is characters 13,10 (CR, LF) and is the
> standard DOS sequence for end-of-line.
>
> Now when I try to geocode the adresses from phpmyadmin I got problems and
> now I need help to search for this code and delete it to my script can
> geocode the adresses without problem. I could do it manually but the
> database has about 600 post. Can someone help with the code to use,
> please?
>
> It look like this in phpadmin [screencast.com] and it should looke like
> this [screencast.com]
>
> /Tine
>

Re: How to delete /r/n after using Navicat in phpmyadmin/Navicat?

Tine Müller wrote:
> I have converted a xml-file to mysql with Navicat but the problem with the
> xml-file was that the end of line code wasn't changed before I used Navaicat
> so I had to look for it manually and change it in there but in Firebug I
> still saw this code \r\n.
>
> I have been told that this \r\n is characters 13,10 (CR, LF) and is the
> standard DOS sequence for end-of-line.
>
> Now when I try to geocode the adresses from phpmyadmin I got problems and
> now I need help to search for this code and delete it to my script can
> geocode the adresses without problem. I could do it manually but the
> database has about 600 post. Can someone help with the code to use, please?
>
> It look like this in phpadmin [screencast.com] and it should looke like this
> [screencast.com]

UPDATE table SET column=REPLACE(column, '\r\n', '');

I do strongly suggest you do make a backup of the table before you will remove
the "carriage return/new line".

--

//Aho