如何检查我在 SQL Server 中的数据是否有回车和换行？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10091827/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to check my data in SQL Server have carriage return and line feed?
提问by TOMMY WANG
Facing a problem, it seems my data stored in SQL Server does not stored correctly, simply put, how to verify that a varchar data has carriage return and line feed in it? I try to print them out, does not show the special characters. Thanks
遇到一个问题，我在SQL Server中存储的数据似乎没有正确存储，简单来说，如何验证varchar数据中是否有回车和换行？我试着把它们打印出来，不显示特殊字符。谢谢
回答by Paul Sasik
You can use SQL Server's char(n)and contains()functions to match on field contents in your WHEREclause.
您可以使用 SQL Server 的char(n)和contains()函数来匹配WHERE子句中的字段内容。
carriage return: char(13) line feed: char(10)
The following SQL will find all rows in some_tablewhere the values of some_fieldcontain newline and/or carriage return characters:
以下 SQL 将查找包含换行符和/或回车符some_table的值的所有行some_field：
SELECT * FROM some_table
WHERE CONTAINS(some_field, char(13)) OR CONTAINS(some_field, char(10))
To remove carriage returns in your some_fieldvalues you could use the replace()function long with char()and contains(). The SQL would look something like:
要删除some_field值中的回车符，您可以使用replace()longchar()和函数contains()。SQL 看起来像：
UPDATE some_table
SET some_field = REPLACE(some_field, char(13), '')
WHERE CONTAINS(some_field, char(13))
To remove new lines you can follow up the last statement with the char(10) version of that update. How you do it all depends on what types of newlines your text contains. But, depending on where the text was inserted/pasted from the new lines may be \r\n or \n so running updates against both \r and \n characters would be safer than assuming that you're getting one version of newline or the other.
要删除新行，您可以使用该更新的 char(10) 版本跟进最后一条语句。你如何做这一切取决于你的文本包含什么类型的换行符。但是，根据文本从新行插入/粘贴的位置可能是 \r\n 或 \n，因此对 \r 和 \n 字符运行更新比假设您获得一个版本的换行符更安全或另一个。
Note that if the newlines were removed and you want to retain them then you have to fix the issue at the point of entry. You can't replace or fix what has been removed so you should save the original text data in a new column that holds the original, unmodified text.
请注意，如果换行符被删除并且您想保留它们，那么您必须在入口点解决问题。您无法替换或修复已删除的内容，因此您应该将原始文本数据保存在包含原始未修改文本的新列中。
回答by Rory Hunter
To add to what others have said; when I need to embed newlines in T-SQL, I tend to do;
补充其他人所说的话；当我需要在 T-SQL 中嵌入换行符时，我倾向于这样做；
DECLARE @nl CHAR(2) = CHAR(13) + CHAR(10);
..then use @nlas required. That's for Windows line-endings, naturally.
..然后@nl根据需要使用。那自然是用于 Windows 行尾。
