1

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
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
FoxyReign
  • 57
  • 1
  • 8
  • 3
    Why are you using `ntext`? Use `nvarchar(max)`. Microsoft documentation specifically warns against using it: https://msdn.microsoft.com/en-us/library/ms187993(v=sql.110).aspx. – Gordon Linoff Nov 16 '15 at 13:27
  • `SELECT REPLACE(CAST(DESCRIPTION AS NVARCHAR(MAX)) ,' ' ,' ') AS DESCRIPTION` – Pedram Nov 16 '15 at 13:29
  • @GordonLinoff I dont have the rights to change the type anymore because it was setup as ntext the first time. – FoxyReign Nov 23 '15 at 15:19

2 Answers2

7

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
Alan
  • 2,962
  • 2
  • 15
  • 18
0

Have you tried this?

REPLACE(CAST(Description as NVARCHAR(MAX)), '
', ' ') AS Description
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786