在 SQL 查询中使用 REPLACE 来获取换行符/回车符
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14836054/
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
Use of REPLACE in SQL Query for newline/ carriage return characters
提问by LCJ
I have a database filed named Account Type that has carriage return and newline characters in it (CHAR(10) and CHAR(13)).
我有一个名为 Account Type 的数据库，其中包含回车符和换行符（CHAR(10) 和 CHAR(13)）。
When I search for this value I need to do a REPLACEas shown below. The following code works fine.
当我搜索此值时，我需要执行REPLACE如下所示的操作。以下代码工作正常。
SELECT AccountNumber,AccountType,
REPLACE(REPLACE(AccountType,CHAR(10),'Y'),CHAR(13),'X') FormattedText
FROM Account
WHERE AccountNumber=200
AND REPLACE(REPLACE(AccountType,CHAR(10),' '),CHAR(13),' ') LIKE
'%Daily Tax Updates: ----------------- Transactions%'
My question is – what are the other characters (similar to CHAR(10) amd CHAR(13)) that would need such a replace?
我的问题是 - 需要这种替换的其他字符（类似于 CHAR(10) 和 CHAR(13)）是什么？
Note: Data type for the column is VARCHAR.
注意：列的数据类型是VARCHAR.
Note: The query is run from SQL Server Management Studio
注意：查询是从 SQL Server Management Studio 运行的
回答by Aaron Bertrand
There are probably embedded tabs (CHAR(9)) etc. as well. You can find out what other characters you need to replace (we have no idea what your goal is) with something like this:
可能还有嵌入的选项卡 ( CHAR(9)) 等。您可以找出需要替换的其他字符（我们不知道您的目标是什么），如下所示：
DECLARE @var NVARCHAR(255), @i INT;
SET @i = 1;
SELECT @var = AccountType FROM dbo.Account
WHERE AccountNumber = 200
AND AccountType LIKE '%Daily%';
CREATE TABLE #x(i INT PRIMARY KEY, c NCHAR(1), a NCHAR(1));
WHILE @i <= LEN(@var)
BEGIN
INSERT #x
SELECT SUBSTRING(@var, @i, 1), ASCII(SUBSTRING(@var, @i, 1));
SET @i = @i + 1;
END
SELECT i,c,a FROM #x ORDER BY i;
You might also consider doing better cleansing of this data beforeit gets into your database. Cleaning it every time you need to search or display is not the best approach.
您还可以考虑在这些数据进入您的数据库之前对其进行更好的清理。每次需要搜索或显示时都清理它并不是最好的方法。
