Solved

Remove "new line"character

Posted on 2010-11-21
7
1,471 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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
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

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.

Question has a verified solution.

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

Suggested Solutions

Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL (http://www.experts-exchange.com/articles/201/Handling-Date-and-Time-in-PHP-and-MySQL.html) several years ago, it seemed like now was a good time to updat…
Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…

806 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