Solved

Remove "new line"character

Posted on 2010-11-21
7
1,472 Views
Last Modified: 2012-08-13
Hi Guys,

I need a MySQL Syntax to remove "new line" character in a field.

I've tried these 2 code, but it not working

UPDATE table1 SET assigned_user_id=REPLACE(assigned_user_id, '\r\n', '');
UPDATE table1 SET assigned_user_id=REPLACE(assigned_user_id, '\n', '');

Please help. Thanks.
0
Comment
Question by:softbless
  • 3
  • 2
  • 2
7 Comments
 
LVL 6

Expert Comment

by:DalHorinek
ID: 34182944
Maybe try

update table1 SET assigned_user_id = TRIM(TRAILING '\n' FROM assigned_user_id)
0
 

Author Comment

by:softbless
ID: 34182946
@DalHorinek it's still working

And i've also tried below, and it's also not working:
update table1 SET assigned_user_id = TRIM(TRAILING '\r\n' FROM assigned_user_id)
0
 

Author Comment

by:softbless
ID: 34182965
I mean it's still NOT working
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 6

Expert Comment

by:DalHorinek
ID: 34182975
Wat about something like UPDATE table1  SET assigned_user_id=TRIM(REPLACE(REPLACE(assigned_user_id, "\n", ""), "\t", ""));


Also try it with simple select

SELECT REPLACE(assigned_user_id, "\n", "") FROM table1

if it does anything
0
 
LVL 26

Expert Comment

by:ushastry
ID: 34182984
Try this...

UPDATE table_name SET column_name = REPLACE(column_name, CHAR(13), '');
0
 
LVL 26

Accepted Solution

by:
ushastry earned 500 total points
ID: 34182991
Actually the new lines are a combination of a new line and a carriage return, namely "\r\n". In this case, you will need CHAR(13) or CHAR(10) can do the trick

Pls backup your table before running any suggested queries..

UPDATE table_name SET column_name = REPLACE(column_name, CHAR(10), '');

or just

UPDATE table_name SET column_name = REPLACE(column_name, CHAR(13), '');
0
 

Author Closing Comment

by:softbless
ID: 34183011
Thanks!
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction In this installment of my SQL tidbits, I will be looking at parsing Extensible Markup Language (XML) directly passed as string parameters to MySQL 5.1.5 or higher. These would be instances where LOAD_FILE (http://dev.mysql.com/doc/refm…
I use MySQL for many of my development projects in a Windows environment. To manage my databases (and perform queries) for years I used a tool called MySQL administrator.  This tool has since been replaced by MySQL Workbench. So I decided to m…
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

820 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question