Solved

Remove "new line"character

Posted on 2010-11-21
7
1,470 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
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 
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

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

A lot of articles have been written on splitting mysqldump and grabbing the required tables. A long while back, when Shlomi (http://code.openark.org/blog/mysql/on-restoring-a-single-table-from-mysqldump) had suggested a “sed” way, I actually shell …
As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
This tutorial demonstrates a quick way of adding group price to multiple Magento products.
A short film showing how OnPage and Connectwise integration works.

919 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now