In this post, we will learn about how to replace newline characters from SQL fields. In this post, we have to use replace function for replace string and also use char function to remove newline characters and replace them. Here I will give you the syntax of replace function, how to use char function, and the meaning of 10 in char function.
Syntax
Description of parameter value,

  • string - Source string
  • string_to_replace - String to search for in string1
  • replacement_string - Replacement string will be replaced string_to_replace with replacement_string in string1

What Char(10) in SQL. Execute the below selected query for checking what char(10) contains. Char(10) displays blank result in SQL query result which means it's \r or \n
SELECT CHAR(10) 

Below query replaces char(10) to html <br /> tag,
REPLACE(EventNote, CHAR(10),'<br />')

See the 4th result in the screenshot for replacing string display with HTML <br /> tag.

HostForLIFE.eu SQL Server 2016 Hosting
HostForLIFE.eu is European Windows Hosting Provider which focuses on Windows Platform only. We deliver on-demand hosting solutions including Shared hosting, Reseller Hosting, Cloud Hosting, Dedicated Servers, and IT as a Service for companies of all sizes.