sql server制表符
This article explores inserting SQL carriage return AKA line break and tab in a string along with SSMS behavior while copying data to excel, notepad.
本文探讨了在将数据复制到excel记事本时，如何在字符串中插入SQL回车符AKA换行符和制表符以及SSMS行为。
介绍 (Introduction)
We work with various data types in SQL Server such as int, float, XML, char, varchar, etc. We also use strings for storing data such as the name of employee, company, product review, and feedback. Sometimes, we require data formats such as inserting a line break, tab or carriage return in a string. We might require these formatting while dealing with data in multiple sources such as flat or excel file. Occasionally, you see scattered text after copying data in notepad or excel.
我们在SQL Server中使用各种数据类型，例如int，float，XML，char，varchar等。我们还使用字符串来存储数据，例如员工姓名，公司名称，产品评论和反馈。 有时，我们需要数据格式，例如在字符串中插入换行符，制表符或回车符。 处理多个来源（例如平面文件或excel文件）中的数据时，我们可能需要这些格式。 有时，在记事本或Excel中复制数据后，您会看到分散的文本。
问题模拟 (Problem simulation)
Let’s simulate the problem using the following query:
让我们使用以下查询模拟问题：
CREATE TABLE [dbo].[CarriageDemo]
([text] [NVARCHAR](100) NOT NULL
)
ON [PRIMARY];
GO
SELECT * FROM [dbo].[CarriageDemo]
In the SSMS output, we see all text in a single line:
在SSMS输出中，我们在一行中看到所有文本：
Let’s copy the output in Excel and Notepad. You can see that in both notepad and excel sheet row one and two splits in multiple lines. It retains the carriage return as well while copying the output in notepad or excel:
让我们在Excel和记事本中复制输出。 您可以在记事本和excel工作表的第一行和第二行中看到多行拆分。 在记事本或excel中复制输出时，它也保留回车符：
复制数据时的SSMS行为 (SSMS behavior while copying data)
We might get different behavior of the carriage return with different versions of SSMS. SSMS 2016 and higher removes carriage return. Without carriage return, the output comes in a single line for each row. SSMS 2014 and previous version retains carriage property, and we are split output across multiple lines:
对于不同版本的SSMS，我们可能会得到不同的回车行为。 SSMS 2016及更高版本删除了回车。 如果没有回车，则每行输出在一行中。 SSMS 2014和以前的版本保留了运输属性，我们将输出分成多行：
- SQL Carriage Return (CR): The Carriage Return moves the cursor to the beginning of the line. It does not move to the next line SQL回车符（CR）：回车符将光标移动到行的开头。 它不会移动到下一行
- Line feed (LF): The line feed moves the cursor to the next line. It does return to the beginning of the line 换行（LF）：换行将光标移动到下一行。 它确实返回到行的开头
SSMS allows us to define the carriage return behavior as well. Navigate to Tools | Options | Query Results | SQL Server | Results to Grid.
SSMS还允许我们定义回车行为。 导航到工具| 选项| 查询结果 SQL服务器| 结果到网格。
In SSMS 2016 and higher, we can see that “Retain CR/LF on copy or save” checkbox is not ticked. It shows that while copying output to notepad or excel sheet, SQL Server does not retain SQL carriage return on copy/save:
在SSMS 2016及更高版本中，我们可以看到未选中“在复制或保存时保留CR / LF”复选框。 它表明在将输出复制到记事本或Excel工作表时，SQL Server在复制/保存时不会保留SQL回车：
Let’s observe the output difference.
让我们观察一下输出差异。
With the enabled option of retain CR/LF on copy or save:
启用保留复制/保存CR / LF的选项：
With the disabled option of retain CR/LF on copy or save:
通过禁用选项，可以在复制或保存时保留CR / LF：
在字符串中插入SQL回车符和换行符 (Insert SQL carriage return and line feed in a string)
We might require inserting a carriage return or line break while working with the string data. In SQL Server, we can use the CHAR function with ASCII number code. We can use the following ASCII codes in SQL Server:
在处理字符串数据时，我们可能需要插入回车符或换行符。 在SQL Server中，我们可以将CHAR函数与ASCII数字代码一起使用。 我们可以在SQL Server中使用以下ASCII代码：
- Char(10) – New Line / Line Break 字符（10） –新行/换行符
- Char(13) – Carriage Return Char（13） –回车
- Char(9) – Tab 字符（9） –制表符
Let’s explore these ASCII codes with CHAR functions with examples.
让我们通过示例探索这些带有CHAR函数的ASCII代码。
插入换行符或换行 (Inserting line break or new line)
Suppose we have a string that contains a month’s name. We use a comma to separate the name of the month. Execute this query in SSMS and view output in Result to text (short cut key CTRL + T) format:
假设我们有一个包含月份名称的字符串。 我们使用逗号分隔月份名称。 在SSMS中执行此查询，并以文本（快捷键CTRL + T）格式查看“结果”中的输出：
DECLARE @strInput VARCHAR(100),
@strResult VARCHAR(100);
SET @strInput = ' January,February,March,May,June,July,August,September,October,November,December'
Select @strInput as Input
Now, we want to insert a line break after each month’s name. We can replace the comma (,) with a char(10) function. As described earlier, char(10) inserts a line break.
现在，我们要在每个月的名称后插入一个换行符。 我们可以用char（10）函数替换逗号（，）。 如前所述，char（10）插入一个换行符。
In the following query, we use the SQL REPLACE function for replacing the comma with char function:
在以下查询中，我们使用SQL REPLACE函数将char替换为char函数：
DECLARE @strInput VARCHAR(100), @strResult VARCHAR(100);
SET @strInput = 'January,February,March,May,June,July,August,September,October,November,December';
SET @strResult = REPLACE(@strInput, ',', CHAR(10));
SELECT @strResult AS 'String with Line Feed';
GO
In the output, we can see a line break after each month. It formats data with a line break, and only one row gets affected due to this:
在输出中，我们可以看到每个月后都有一个换行符。 它使用换行符格式化数据，并且由于以下原因仅一行受到影响：
插入SQL回车 (Inserting SQL carriage return)
We use the Char(13) function for inserting a carriage return instead of a line break:
我们使用Char（13）函数插入回车符而不是换行符：
DECLARE @strInput VARCHAR(100), @strResult VARCHAR(100);
SET @strInput = 'January,February,March,May,June,July,August,September,October,November,December';
SET @strResult = REPLACE(@strInput, ',', CHAR(13));
SELECT @strResult AS 'String with Line Feed';
GO
在字符串中插入SQL回车符和换行符 (Inserting SQL carriage return and line break in a string)
In previous examples, we used Char(10) and Char(13) individually for carriage return and line break, respectively. We can use both functions together for inserting a carriage return and line break:
在前面的示例中，我们分别将Char（10）和Char（13）分别用于回车和换行。 我们可以同时使用这两个函数来插入回车符和换行符：
DECLARE @strInput VARCHAR(100), @strResult VARCHAR(100);
SET @strInput = 'January,February,March,May,June,July,August,September,October,November,December';
SET @strResult = REPLACE(@strInput, ',', CHAR(10) + CHAR(13));
SELECT @strResult AS 'String with Line Feed';
GO
The output of the above query with Char(10) and Char(10) is as shown below:
使用Char（10）和Char（10）的上述查询的输出如下所示：
插入标签 (Inserting tab)
Sometimes we insert tab between characters for formatting purposes. We can insert tab space in a string using the Char(9) function:
有时，我们在字符之间插入制表符以进行格式化。 我们可以使用Char（9）函数在字符串中插入制表符空间：
DECLARE @strInput VARCHAR(100), @strResult VARCHAR(100);
SET @strInput = 'January,February,March,May,June,July,August,September,October,November,December';
SET @strResult = REPLACE(@strInput, ',', CHAR(9));
SELECT @strResult AS 'String with Line Feed';
GO
In the output, you can string format with a tab between each month:
在输出中，您可以在每个月之间使用制表符对字符串进行格式化：
删除换行符 (Remove line break )
Suppose we have a table that contains data with a line break. In the following table, we have a line break in the address column:
假设我们有一个表，其中包含带换行符的数据。 在下表中，地址列中有一个换行符：
CREATE TABLE [dbo].[EmployeeData]
([EmpID] INT IDENTITY(1, 1),
[FirstName] NVARCHAR(20),
[LastName] NVARCHAR(20),
[Address] NVARCHAR(100)
);
INSERT INTO [dbo].[EmployeeData]
(FirstName,
LastName,
Address
)
VALUES
(N'Rajendra',
N'Gupta',
N'123,
Maruti Kunj,
Gurgaon'
);
We use Char(13) for identifying and removing Carriage Return and Char(10) for removing line break along with the SQL REPLACE function. The replace function replaces line break with a space as specified in the query:
我们使用Char（13）来识别和删除回车符，并使用Char（10）来删除换行符以及SQL REPLACE函数。 replace函数将换行符替换为查询中指定的空格：
Select EmpID, FirstName, lastName,
REPLACE(Address,CHAR(13)+CHAR(10),' ') as address from EmployeeData
In the output, we can see the address field without the line break.
在输出中，我们可以看到没有换行符的地址字段。
结论 (Conclusion)
In this article, we explored the process for adding and removing a SQL carriage return and line break from a string. We also learned about the SSMS behavior for retaining carriage return and line break while copying the output to notepad or excel sheet.
在本文中，我们探讨了从字符串中添加和删除SQL回车符和换行符的过程。 我们还了解了SSMS在将输出复制到记事本或Excel工作表时保留回车符和换行符的行为。
翻译自: https://www.sqlshack.com/sql-carriage-return-or-tab-in-sql-server-string/
sql server制表符
扫一扫
569
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
