New Line in SQL Query

New line in Sql Query

Pinal Dave explains this well in his blog.

http://blog.sqlauthority.com/2009/07/01/sql-server-difference-between-line-feed-n-and-carriage-return-r-t-sql-new-line-char/

DECLARE @NewLineChar AS CHAR(2) = CHAR(13) + CHAR(10)
PRINT ('SELECT FirstLine AS FL ' + @NewLineChar + 'SELECT SecondLine AS SL')

How to insert a line break in a SQL Server VARCHAR/NVARCHAR string

I found the answer here: http://blog.sqlauthority.com/2007/08/22/sql-server-t-sql-script-to-insert-carriage-return-and-new-line-feed-in-code/

You just concatenate the string and insert a CHAR(13) where you want your line break.

Example:

DECLARE @text NVARCHAR(100)
SET @text = 'This is line 1.' + CHAR(13) + 'This is line 2.'
SELECT @text

This prints out the following:

This is line 1.

This is line 2.

New line in sql server

Try to use CHAR(13) -

DECLARE 
      @firstName NVARCHAR(50) = '11'
    , @lastName NVARCHAR(50) = '22'
    , @text NVARCHAR(MAX) 

SELECT @text = 
    'First Name : ' + @firstName + 
    CHAR(13) + --<--
    'Second Name : ' + @lastName

SELECT @text

Output -

First Name : 11
Second Name : 22

Is it safe to add a new line charactor (\n) in the sql query?

Yes, it is perfectly fine to add newlines (\n or \r\n) to your query. Most parsers consider newlines as whitespace (just like a space or tab), and so does the one used by Oracle (and all other database I have used).

You have already proven this, because based on your comments your company already does this (and probably has so for years). I'm curious as to why you think it could be unsafe. Because obviously it works, and if it would be unsafe, Oracle would disallow it with a clear error.

Although very subjective, it could be better than not doing it, as it allows for a easier visual inspection if all lines end in whitespace. Consider the difference between:

1. Oops, no space

   "select *" +
   "from table"  
   

2. Space

   "select * " +
   "from table"
   

3. Linebreak

   "select *\n" +
   "from table"
   

The difference between 1 and 2 is smaller than between 1 and 3. Especially in long queries this might matter to see if you forgot whitespace which might either lead to a syntax error, or worse to incorrect query behavior.

On the other hand, the newlines are extra visual clutter that might distract from reading the query. When in doubt, go with what is the norm or codestyle in your company.

How can I check if an SQL result contains a newline character?

SELECT *
FROM your_table
WHERE your_column LIKE '%' + CHAR(10) + '%'

Or...

SELECT *
FROM your_table
WHERE CHARINDEX(CHAR(10), your_column) > 0

How to correctly insert newline in nvarchar

The problem is your setting on SSMS, not that the data doesn't have a line break.

Go to: Tools -> Options -> Query Results -> SQL Server -> Results to Grid -> Retain CR/LF on copy or Save and make sure the option is ticked.

SQL: Insert a linebreak in varchar string

Well your query works perfectly fine.
SSMS by default shows all query out put in the grid view, which does not display the line break character.

To see it you can switch to text view using cntrl + T shortcut or like below

The results I got for your query are below( and they work)

Replace new line from the end of string SQL and update columns

Solution

The problem was that I had /r at the end of each string, not \n. So I had to use CHAR(13) instead of CHAR(10).

UPDATE Players
SET Name = REPLACE(Name, CHAR(13), '')

Also to remove all line feed characters (\n) I used:

UPDATE Players
SET Name = REPLACE(Name, CHAR(10), '')

Moreover to remove all the spaces () I used:

UPDATE Players
SET Name = REPLACE(Name, ' ', '')

---

Related Topics