Replacing carriage-returns & line-feeds from a string/column in Oracle/Database.

To replace the carriage-returns & line-feeds from a string in Oracle or database, replace() does not work properly.

There is a special handling required to replace new line or carriage-returns.


If the data in your database is POSTED from HTML form TextArea controls, different browsers use different New Line characters:

• Firefox separates lines with CHR(10) only
• Internet Explorer separates lines with CHR(13) + CHR(10)
• Apple (pre-OSX) separates lines with CHR(13) only

So you may need something like:

• update table_name set col_name = replace(replace(col_name, CHR(13), ''), CHR(10), '') ;

- Cheers!!!

message from server: "Host is not allowed to connect to this MySQL server"

Today I came across the problem for remotely connecting to MYSQL database from an application hosted on different machine.

Solution for this issue is to run the following query for granting privileges  to desired user:

GRANT ALL PRIVILEGES ON *.* TO '<USER_NAME>'@'%' IDENTIFIED BY '<PASSWORD>' WITH GRANT OPTION;

Enjoy!!!