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:
Oops, no space
"select *" +
"from table"Space
"select * " +
"from table"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
Dynamic Sorting Within SQL Stored Procedures
Add Unique Constraint to Combination of Two Columns
Transfer Data from One Database to Another Database
How to Return Multiple Rows from the Stored Procedure? (Oracle Pl/Sql)
Select for Update with SQL Server
How to Use SQL Order by Statement to Sort Results Case Insensitive
How to Insert Data into Two Tables Simultaneously in SQL Server
SQL Server Select to JSON Function
Polymorphism in SQL Database Tables
Differencebetween Function and Procedure in Pl/Sql
How to Pass Parameters to Query in SQL (Excel)
Select Random Row(S) in SQLite
SQL Server Equivalent to MySQL Enum Data Type
What Does the "@" Symbol Do in SQL
Google Spreadsheet "=Query" Join() Equivalent Function
Bulk/Batch Update/Upsert in Postgresql
Which Is Better: Ad Hoc Queries or Stored Procedures
Rewriting MySQL Select to Reduce Time and Writing Tmp to Disk