Replace newline in ntext

sql sql-server replace sql-server-2012 ntext
FoxyReign picture FoxyReign · Nov 16, 2015 · Viewed 7.9k times · Source

One of the columns uses ntext. I want to replace the newlines and carriage returns in to space but I can only do it one at a time. Is there a way I could use CHAR(10) and CHAR(13) at the same time using the script below on SQL Server 2012?

REPLACE(CAST(Description as NVARCHAR(MAX)), CHAR(10), ' ') AS Description

Answer

Alan picture Alan · Nov 16, 2015

To replace CRLF instances, you could use:

REPLACE(CAST(Description as NVARCHAR(MAX)), CHAR(13)+CHAR(10), ' ') AS Description

To individually replace all CRs and LFs, you could use:

REPLACE(REPLACE(CAST(Description as NVARCHAR(MAX)), CHAR(10), ' '), CHAR(13), ' ') AS Description

Related questions

UPDATE and REPLACE part of a string

I've got a table with two columns, ID and Value. I want to change a part of some strings in the second column. Example of Table: ID Value --------------------------------- 1 c:\temp\123\abc\111 2 c:\temp\123\abc\222 3 c:\temp\123\abc\333 4 c:\temp\123\…

Read More
SQL Server: How do you remove punctuation from a field?

Any one know a good way to remove punctuation from a field in SQL Server? I'm thinking UPDATE tblMyTable SET FieldName = REPLACE(REPLACE(REPLACE(FieldName,',',''),'.',''),'''' ,'') but it seems a bit tedious when …

Read More
How do I UPDATE from a SELECT in SQL Server?

In SQL Server, it is possible to insert rows into a table with an INSERT.. SELECT statement: INSERT INTO Table (col1, col2, col3) SELECT col1, col2, col3 FROM other_table WHERE sql = 'cool' Is it also possible to update a …

Read More