Then the commas are replaced by the line break character CHAR(10). How it works: Paste your chunk of text in a (new) MS Word Document; Press CTRL + H for the replace form. Question: I want to remove the line feed characters from data inside my tables and chop-off everything after the end-of-line character. I designed the SQL  1 Jun 2009 I prefer this way instead of concatenating the entire CHAR(13)+CHAR(10) to the end of every string. Over here we are using three types of control character. SELECT CHAR (10) + cats FROM T_Cats. You can do a regular expression find and replace on \x0d\x0a, which represents a line break. I use VBA to do to replace these characters in Access. I'm thinking I need a regex expression, but am not sure how to code it. Now it is Access. Put ^(\r )+ This minimizes the number of occurrences of changes  Dec 31, 2010 · I am trying to bring the SY03900. Nov 12, 2018 · Replacing special characters CR and LF while loading data using SQL Loader Hello everyone,I am looking for some much needed advice on what options I can use to replace special characters Carriage Return (hex 0D) and Line Feed (hex 25) when loading them into Oracle using SQL Loader. Using the find and replace within Notepad++, we can easily change back and DBAs@Midnight Webshow &mi nl2br — Inserts HTML line breaks before all newlines in a string of characters; str_replace() - Replace all occurrences of the search string with the replacement   I would like to replace these with a different character during my export, but I can not get the charindex function to select on this. SomeColumn, ''), CHAR (13), ''), CHAR (10), ' ') You just concatenate the string and insert a CHAR(13) where you want your line break. I prefer to replace line-breaks with a single space, rather than removing them. To get them all, you need something like this:  26 Jul 2018 How To Replace Newline Character From The SQL Server Field · string - Source string · string_to_replace - String to search for in string1  23 Aug 2011 Remove and Replace Carriage Returns and Line Breaks in Access. 15 Jan 2020 %let mvPatternTxt = nullTxt; proc sql; select TEMPLATE_TXT into Have you considered a replacing the line breaks with a character that isn't  15 Nov 2005 Remove NewLine characters from the data in SQL Server. Mar 20, 2012 · Clearing line-breaks in SQL Server text fields I was recently doing a data export for a client that included a bunch of ostensibly plain-text data fields that include any number of tabs, carriage returns and line feeds that were mucking up the csv file. Mar 04, 2020 · The "notes" field of your CSV file must have some indication of where the line breaks should be. substring is the substring to be replaced. Sep 13, 2009 · Page 1 of 2 - new line / break line - posted in Ask for Help: hi, im a new ahk coder. Indeed, the CHAR(13) and CHAR(   Replace a newline in TSQL, Actually a new line in a SQL command or script string can be any of CR, LF or CR​+LF. Sep 10, 2017 · That is why I use both chars as line break: Declare @LineBreak nvarchar(10) = char(13)+ Char(10), @text nvarchar(100); Select @text = ‘This is the First Line’ + @LineBreak + ‘Second Line’; Using it like this, It works in Linux, Unix and old IOS tooo. Then I retrieve that data. Find What: (\r )+ 3. Drop table T11 create Table T11(Col1 Varchar(MAX)) Insert into T11 Select 'X'+CHAR(13)+'Y' Select Replace(col1,Char(13),'') From T11 where Col1 like 'X%' Code language: SQL (Structured Query Language) (sql) In this syntax: input_string is any string expression to be searched. The following 3 supported characters which are given below, but CHAR(10) is for creating a new line. on what options I can use to replace special characters Carriage Return (hex 0D) and Line Feed (hex 25) when loading them into Oracle using SQL Loader. not break in your SQL Server Reporting Services Oct 26, 2016 · If you’re building a string using an SQL statement, you can split results with “new lines” using CHAR () to insert the ASCII line break character into your string. char(9) is the TAB character. We have three sentences in the cell A1, and each sentence is separated by a comma (,). "How to replace a character with a new line in vscode?" I feel curious about what exactly the issue is, is there any more detail description of it? @ruancra Yes, this is expected. ", "This is line #2. In many places on approach to allowing quotes inside of text values is to use a doubled character "This string contains a "" in the body of the line" will resolve to: This string Thanks Jesper, you is the man. ) by using the Find and Replace with “Special” function. Indeed, the CHAR(13) and CHAR(10) specific codes represent the  4 Nov 2019 This article explores inserting SQL carriage return, line break and tab in SQL REPLACE function for replacing the comma with char function:  21 Dec 2017 Quick examples for adding special characters like Line Break, Carriage Return and Tab You just need to replace CHAR(10) with CHAR(13). a string of characters that will be replaced or removed from source_string. For example: 21 Feb 2014 'Remove CR and LF characters from string, replace with spaces ssuming the last two characters of these troublemaker fields is a carriage return followed An Access backend (probably not), SQL/Server, something e How to Remove New Line Character in Multilne String. Getting started with SQL Server So the replace would be \n, to put a newline before the the comma, or ,\n to follow the comma with a line break. Inserted in to sql as a string, returns this on select: Here is a well formatted comment. Otherwise, tabs get converted into spaces when this page is rendered. If omitted, start is 1: count: Optional. Snowflake Replace Function to Remove Newline Characte 1 Apr 2015 Hi Friends, Today, I will share a small tip to replace Carriage Return, GO -- replace carriage return (CHAR(13), tab (CHAR(9) and line feed  The \n escape sequence represents \x0a which is a line feed. replace_string. Carriage return/paragraph mark ^14 or ^n. 10 Sep 2017 This is due to the fact that char(13) is carriage return, but the newline character in Linux is char(10), otherwise known as “line feed”. Nils. So step one is to replace all the line feeds with carriage return plus line feed, to remove all the square symbols. Don't forget to slip in CHAR(10) and/or CHAR(13) for the line break. In the aforementioned example, we can use the TRANSLATE, a new SQL Server 2017 function. I'm trying to remove this with the folloiwing select statement oreplace (cust_id, '0A'XC, '')as custid. I insert data that contains a line feed character into the database. REPLACE() Function to remove the CRLF Character from a String. It's not necessary to use a Macro for changing line breaks because  Example. ' + CHAR(13) + ' This is line 2. I am using the following to preserve line breaks =REPLACE(Fields!txtfield. update Replace or remove all line breaks (or new lines) from a column. The SQL CHAR  Getting started with SQL Server. Actually, here is presented a problem dealing with character field that consist of mysterious "hidden" blank spaces. Reply. Table]. So, I wanted to replace line feeds (char(10)) and carriage returns (char(13)),  You can use the REPLACE function in the SQL selecting the free text column to remove the line break/carriage return: REPLACE(col, CHAR(13) + CHAR(10), ''). Example: DECLARE @text NVARCHAR(100) SET @text = 'This is line 1. ) John Jul 04, 2019 · Using SQL statement, If you are trying to build a string then you can use CHAR() to insert the ASCII character for the line break into your string. The expression \AL matches only the first L character in the string Line1 Line2 , regardless of whether the search is in single-line or multi-line mode. com The formulas themselves have it's inidividual components separated by semi-colons in one of the columns, and I want to use this character as a line-break, so that each formula part is shown on a seperate line within the cell. When you use vbCrLf within two string or values, like, you have in the following code, it inserts a new line. In multi-line mode, that is, when embedded newline characters in a string are considered the termination of a line, \A does not match the beginning of each line. Last I have a text string being passed to me as an inbound parameter. You can directly use Substitute Function to replace line break with a space or with nothing. 4 Solutions. 2. as I typed in the text box. Otherwise, you need to replace whatever does indicate a line break with "`r`n". All I need to get that to work in a macro is a way to load the clipboard with the copied line break I have tried all possible ways using Find and replace, but the Replace box always refuses to recognise the copied soft line break, and does not accept \x000A or \#09, so it pastes a nul, which effectively deletes the paragraph return. perhaps there is a SQL line I can put in a query. It's not necessary to use a Macro for changing line breaks because MS Word allows the "carat p" symbol ^p (carat is the <shift> 6 key) to represent a carriage return in an MS-Word replace command. Mar 19, 2015 · Removing a line break character in a column Hello, I have a problem with a field in my data where the field seems to contain a line break or a carriage return. CHAR(10) = Line Feed CHAR(13) = Carriage Return CHAR(160) = Non-Breaking Space. Column break? Question mark ^-Optional hyphen ^~ Nonbreaking hyphen ^^ Caret character ^+ Em dash ^= En dash ^m. The substring to replace find in string1: start: Optional. an optional string of characters that replaces the characters specified by search Also, I used NCHAR(0x09) for the tab characters in the @Test variable so that the example code can be copied-and-pasted and retain the correct characters. This is line 2. I tried Nov 27, 2018 · We can see that the REPLACE function is nested and it is called multiple times to replace the corresponding string as per the defined positional values within the SQL REPLACE function. There is a video below, that shows the steps. Value,chr(13),vbCrLf) (this formula is from a newsletter from Victoria Yudin's Flexible Solutions. Excel Remove carriage return and remove line break. SELECT REPLACE([Column], char(9), '') From [dbo. The REPLACE() function returns a new string in which all occurrences of the substring are replaced by the new_substring. 7. ) Mar 25, 2020 · The tutorial will teach you three quick and easy ways to add a line break in Excel cell: use a shortcut to type multiple lines, Find & Replace feature to add a carriage return after a specific character, and a formula to combine text pieces from several cells each starting in a new line. Is that you do not want linefeed in between the character? Then you can use replace function. In the code shown above, we look for all Newline and Tab characters and replace it with a ‘-‘ and ‘’ respectively. To find a line break, and replace it with a space character, follow these steps. Nov 04, 2019 · We use Char(13) for identifying and removing Carriage Return and Char(10) for removing line break along with the SQL REPLACE function. to Sender' ELSE Substring(Replace(Replace(SMTPBounceReason, Char(10), ''), Char(13), ' '), 1, SQL Query: DATAVIEW_BOUNCE; DataExtract: DATAVIEW_B 13 Aug 2018 line break one or two characters · SQL when (re)loaded and saved again with SQLNAV 6. I have tried numerous ways to Replace, Split, etc, but running into a road-block. creating a page break, em dash, column break, etc. Manual page break ^s. 26 Oct 2016 If you're building a string using an SQL statement, you can split results with “new lines” using CHAR() to insert the ASCII line break character  16 Dec 2019 Regular expressions allow the user to find, replace, and manipulate text based Regular expressions in SSMS (SQL Server Management Studio) allow us to \r: Find a carriage return character; \n: Find a line feed chara 12 Jan 2019 The term CRLF refers to Carriage Return (ASCII CHAR(13), \r) Line Feed Now we will see we can remove CRLF from a String in a Column in SQL Server. Another version of the post with a more complete example is also available, it shows how to remove line breaks from a string in a table. As you can see above, it has reduced the width of the row and increased the height of the row by placing a carriage return character. A powerful feature of UltraEdit and UEStudio is their wide range of find/replace features. Accordingly, you can create a REPLACE  13 Jun 2017 To remove these SQL line break, use the SQL Server replace function. What we need in this case is we need to push the second line to the next line and third line to the next line like the below one. Apr 07, 2017 · how to replace the line break with tab space in database in all the column of the database We have one database which has 10 table contains 10 columns in each table, which contain Line break data( ) in 5 Columns I Want all the 5 different columns data to be replaced with spaces if it contains line break. I prefer this way instead of concatenating the entire CHAR(13)+CHAR(10) to the end of every string. Toggle Wpautop. 2) line feed – Char(10) or vbLf. I can neither predict or control the length of the test string. If search_string is NULL, the function returns source_string. =REPLACE (@CarriageReturn_LineFeed,'[\r\n];',CHAR(13)+CHAR(10)) 9 Jan 2021 How to remove SQL Server line break from a text? Breaks or replace it with a different character words, remove line breaks in HTML files . The start position in string1 to begin the search. Aug 18, 2018 · New Line / Line Break: Char(10) Carriage Return: Char(13) Tab: Char(9) Inserting Line Break or New Line. You can find a string of text in a file, in multiple files, all open files, project files, throughout a directory and its sub-directories, and more. Look into the replace or translate function. Example: DECLARE @text NVARCHAR(100) SET @text = ' This is line 1. The carriage return character is \x0d. Do CTRL-H to bring up the Find & Replace dialog; Click on "More Options" (bottom left button) jQuery - replace line break <br /> with new line ; Detect a new line character; powershell replace command if line starts with a specific character; How can I replace a new line character with a null terminator? Regex: replace groups of 4 spaces (from new line until a character) with tabs; Split the string with new line character in gwt 2. Is there a way to do a simple Find & Repace to turn those characters into spaces? OR . If omitted, it will replace all occurrences of find with replacement: compare: Optional. Insert line breaks where newlines (\n) occur in the string: <?php echo nl2br("  18 May 2018 Oracle SQL select command from a table that contains newline characters (liked \ n, \r, \t) and replace it with a space " ". It returns NULL if Thus all the paragraph breaks in you document will get replaced by a comma. (Please replace personal information) If you want to add line break into an array, then convert it into a string, maybe you could consider add URI code "%0A" into it, then use decodeUriComponent() function to decode it, then it will break line: decodeUriComponent(variables('testString')) Run result: Best Regards, Community Support Team _ Lin Tu vbCrLf constant stands for Carriage Return and Line feed, which means Cr moves the cursor to the starting of the line, and Lf moves the cursor down to the next line. Excel uses the line feed character Chr(10) to make a new line in a cell, but Access uses both the carriage return and line feed characters: Chr(13) & Chr(10). The problem is that you cannot specify this carriage return symbol on a replace command. SQL · database,; line Control Character | T-SQL Reprentation | Abbreviation | Escape code |. See full list on docs. Oracle - SQLLoader offer two possibilities : if you have a special character at the end of each line: the stream record format Using the STR attribute, we can specify a new end-of-line character (or sequence of characters). new_substring is the replacement string. The following code sets up the data with line breaks and then replaces them when selected in SQL: Dec 08, 2015 · You just concatenate the string and insert a CHAR(13) where you want your line break. You can use whatever character (s) you like in “Replace with” box. We use Char(13) for identifying and removing Carriage Return and Char(10) for removing line break along with the SQL REPLACE function. as I typed in the The new line often consists of the \r characters, so in Martin's reply try to replace the sub-point 2. Below you can find the Custom Code I used to solve adding line break between words in column headers. Jun 13, 2017 · To remove these SQL line break, use the SQL Server replace function. I want to insert this value into the table where I need to use the special character '&'. Dec 04, 2015 · As this article is related to special characters I have a query while inserting a record. Sources: What's the best way to identify hidden characters in the result of a query in SQL Server (Query Analyzer)? You may struggle to load data with embedded Characters - Newline - End of Line (EOL) - Line Separators - Line Break. 5. SQL HOME SQL Intro SQL Syntax SQL Select SQL Select Distinct SQL Where SQL And, Or, Not SQL Order By SQL Insert Into SQL Null Values SQL Update SQL Delete SQL Select Top SQL Min and Max SQL Count, Avg, Sum SQL Like SQL Wildcards SQL In SQL Between SQL Aliases SQL Joins SQL Inner Join SQL Left Join SQL Right Join SQL Full Join SQL Self Join SQL Simply press Ctrl + Enter to insert a new line into the find or replace dialogs. This can be done easily  10 Nov 2020 Remove Newline (n) Characters from String in Snowflake, remove carriage return (r) such SQL Server , then it can contain line breaks or newline (\n) characters. Monday, August 18, 2008 2:56 PM Code Snippet. See ASCII codes here. And then replace this single space with line break and return the evaluated parameter as output. 1,161 Views. There is a product like 'HAIR & CARE'. Unicode-linebreak A Ruby Implementation Of The Tags Once Again. To transform this into an HTML line break, we have to do a bit more work than just replacing the %0A value with <br/>. as test1 from dummytable ; quit; to address line 2 over line 1 issue. 1? Maybe there should be an english edition of this issue. CHAR(10) – LineFeed. 3) carriage return + line feed – Char(13)& Char(10) or vbCrLf . SELECT CAST ( '\n'  Marketing Cloud Tracking Extract for Bounce data has line breaks. In this example, I’ve generated a string with comma delimiters. Update table name set description= replace(replace(description,CHAR( 10),'') , CHAR(13),'') ;. Where "n Nov 27, 2018 · Again, you have to replace the newline character with an HTML line break, but in this case the character is the URL encoded variant of Char(10), which is %0A. Sep 06, 2012 · Sometimes you might need to remove line breaks from a column value to make a one-line data. When I am using '&' character, the SQL is prompting for the value to enter which I dont want. I am using this script to attempt to remove the line feed while selecting the data from SQL: Select Replace(Replace stringname,char(10),'',char(32),'')) from tablename The replace function seems to execute, but it does not remove the line feed correctly. The type of string comparison. Replace With: \r or better in Nilesh's answer try to replace the sub-point 3. Possible values: a character string or string expression. Find and Replace a Line Break. This replace function also comes in handy  2 May 2010 Carriage return returns the carriage with the typing paper to the left margin. ' SELECT @text This prints out the following: This is line 1. If I understand correctly (and asterisk is a literal * rather than a wildcard), then you need to:. I am searching this replace query for a long time. If the question is not that, please explain. ' + CHAR(13) + 'This is line 2. Required. Microsoft SQL Server 2008; Microsoft SQL Server; 13 Comments. I get create or replace procedure pro_run is. The number of replaces to perform. Example: Here is a well formatted comment. CHAR(9) - Tab. g. C. I would like to preserve both the line breaks and the tabs that have been entered in GP. CR = Carriage Return; LF = Line Feed After that, you will be able to see the end of line characters and see which ones are used. You can create a function, either as a SQLCLR Scalar UDF or a T-SQL Inline TVF Users insert comments etc, using line breaks, however because I just pass this as a string the line breaks are not there when they view the text box after input. I knew of the REPLACE function, but hadn't thought about the fact that tab & newline have char values. I really appreciate your help, many thanks and have a great Dec 19, 2019 · I inherited a database where the editor inserted a line break or paragraph break (ASCII characters 10 or 13) at the end of every line instead of letting word wrap work. For anyone using SQL Server 2016 or older. For example, if you were wanting to use a word mail merge to list all your cats on there own line you would use the following. If source_string is NULL, the function returns NULL. A newline or line break is CHAR(13) + CHAR(10). After some searching online I found this method that uses MS Office Word and the replace functionality. e. Remove P D. There exists more hidden character, but these are the most often. This allows us to create an input data file that has Feb 11, 2016 · You can escape embedded line breaks with plain-characters by doing something like this: REPLACE(column_name, NCHAR(13) + NCHAR(10), '/r/n') Your data recipient must be aware you are doing this however, so they will have a chance to reverse this replacement before they store the data as not to compromise the original value. microsoft. I'd like to rtrim this selection for line breaks, but I don't know how to remove the line feed characters: Have you considered a replacing the line breaks with a character that isn't used in the same variable and then create a line break when required? E. I would like to replace only LF with a space, but leave CRLF in place. will see that character 10 is the new line character. search_string. txtfield onto a SSRS report. Requesting Gravatar Thank you. Character. Question by David 2 1 · Jan 20, 2011 at 02:27 AM · t-sql sql-server-2000 select How To Select Data After A Character I have a massive character string from which I'd like to extract data (for example, all the To Denote A Single Line Break, Unix Programs Use Line Feed, Whose Hexadecimal Value In ASCII Is 0a, While Most Programs Common To MS-DOS And Microsoft Windows Use Carriage Return + Line Feed, Whose Hexadecimal Value In ASCII Is 0d 0a. Char(13) - CarriageReturn. The replace function replaces line break with a space as specified in the query: Nov 27, 2012 · remove tab, new line and return carriage from string (t-sql) I've been facing this issue for many years now and it came back again this morning. Best would be to place the string "`r`n" everywhere a new line starts. There Are Many Other Plugins Available As Well, But These Are Some Of The Most Popular Available Plugins. Nonbreaking space ^nnn. 2 Answers2. Line Feed Now lets see how we can create new line in SQL Server. Char(10) newline character char(13) carriage return first  20 Mar 2012 SELECT replace([field],char(10),'[replacementchar]') FROM [table]. Oct 19, 2013 · Your question is not clear. In ASCII, Carriage Return Is A Distinct Control Character. Indeed, the CHAR (13) and CHAR (10) specific codes represent the characters of the line break, and then replace them with space or a comma, for example: SELECT CustomerID, FirstName, LastName, REPLACE(AddressText,CHAR(13)+CHAR(10),' '), CountryCode, CountryName. Page or section break (Replaces a section break with a page break) ^13 or ^p. Enter the character(s) that you want to replace with a line break. SM17CH asked on 2010-05-02. For example, if you want to replace every paragraph break with two paragraph breaks –then you should type ^p^p in “Replace with” box. Tab ^11 or ^l. Select the cells that you want to search Sql Remove Specific Characters From String TRIM() is a T-SQL function that removes the space character char(32) or other specified characters from the start or end of a string. Feb 14, 2014 · acknak Thanks - that is very neat. The replace function replaces line break with a space as specified in the query: The following code will remove linefeed characters in SQL and replace them with a zero-length string: UPDATE TableName SET FieldName = REPLACE (FieldName,CHAR (10),'') Remove and Replace Carriage Returns and Line Breaks in Tableau Use CHAR (10) and CHAR (13) to remove a line feed or carriage return in Tableau Dec 23, 2020 · At the core we are using the built in REPLACE function. So, in an update query for the problem field set the update line to: MS Word Replacing Characters with Line Breaks You can use the Find and Replace in MS Word to replace any characters with a Line Break or other special characters or properties (i. Match a single character that is a “whitespace character” (spaces, tabs, and line breaks) Equivalent to [ \f \r\t\v]. Adding Code To The The SQL - Replace line break with character. com In the Quick Find Replace, Open the (+) Options portion of the find / replace Clink on Regular Expressions for the find use the comma (,) for the replace use the new line( ) followed by the comma(,) So the replace would be , to put a newline before the the comma, or , to follow the comma with a line break Jan 01, 2015 · Replacing the carriage return char (13) and the line feed char (10) To avoid such issues, you need to clean the data by replacing the carriage return (char (13)) and line feed (char (10)) in your SELECT statement using the following query: SELECT replace (replace (CountyCode, char (10), ''), char (13), '') FROM [MSSQLTipsDemo]. Jun 09, 2017 · Sometimes I need to add (a lot of) line breaks in some chunk of text. @Comments I need to take this value and break into individual lines of 80 char max. You can use the same VB script in adding line breaks among any where in your reporting services reports. You can replace them to ' ' or Char(32), it depends how you want to handle it. At the bottom of the "Find and Replace" dialog, check the box "Use" and select "Regular expressions". I love ahks simplicity! I have a question: How do you make a new line / break line? I was using FileAppend, and wanted the text to break into several lines. There are several ways to change line in a Cell: 1) carriage return – Char(13) or vbCr. String ^9 or ^t. May 28, 2013 · Why does that work? A line break is character 10 in the ASCII character set, and the Ctrl + J shortcut is the ASCII control code for character 10. 21 Aug 2010 Requirement I had a requirement to take extract of all Contacts from Siebel Database (Oracle Database in this case). myusql. Maybe that will help. "); proc sql; select &test. today. Jul 26, 2018 · Open the file using Notepad++ (or paste the text into a new file) Open the Search -> Replace menu In the ‘Find what’ box enter the character to convert to a new line In the ‘Replace with‘ box enter See full list on sqlshack. */. %let test = catx('0D0A'x, "This is line #1. OUTPUT Jun 01, 2009 · SQL Cast Date » Here is a clean and efficient way to embed carriage returns into a string. New line ^12. If this question in any way related to the one you [asked on May 9][1], you may notice that in the answer the line break is replaced with single space, not empty string, To make your solution work you might want to consider this: When replacing characters, opt to replace tab (char(9)), line feed (char(10)) and non-breaking space (char(160)) with single space. If you notice the result, it’s formatted with line break and finally it says 1 rows affected. Some details about line break here: Replacing carriage return and line break in a string ‎01-30-2018 11:42 AM I am pulling in a field from a database table through a SQL Server connection that contains all the fields that populate a dropdown - they are separated by a carriage return and a line feed. Sep 15, 2014 · Incoming txt file has mix of LF and CRLF. [added] I really couldn't tell what you were trying to 1 Mar 2010 The {CR}{LF} are control characters which denote 'carriage return' and This SQL Statement uses a combination of CHAR and REPLACE to  15 Apr 2005 Use REPLACE. This is how to do it with Transact-SQL: REPLACE (REPLACE (ISNULL( SomeTable. Remove SQL line breaks from text with a simple query, To remove these SQL line breaks, use the SQL Server replace function. \S [^:Wh] Match a single character that is a “non-whitespace character” [0-1] Match a single character in the range between “0” and “1” Character range: Matches any single character in the range from first to last. [dbo]. This drops all carriage returns and line-feeds: SELECT REPLACE(REPLACE(log, '\r', ''), '\n', '') FROM test; Couple with  27 Nov 2012 Actually, here is presented a problem dealing with character field that consist of mysterious "hidden" blank CHAR(13) - Carriage Return. chr(10) is a linefeed and chr(13) is a carriage return. The Newline in T-SQL is represented by CHAR(13) & CHAR(10) (Carriage return + Line Feed). Jan 13, 2019 · Result of the SQL Query before and after applying the replace function. Check this article for more informations on the special characters and the char() T-SQL function here on Microsoft official website. (If you don't really need the numbers at the start of each line, Eirikur's solution will work fine.