在字符串中搜索换行符并最终删除换行符的SQL查询

Question

如何在字符串中查询换行符并移除它？

我有一张表格里面的某些数据含有不想要的换行符。我正在尝试编写一个查询语句来获取所有包含换行符的字符串。

我尝试了以下语句：

select * from Parameters
where Name LIKE '%"\n" %'

同样也

select * from Parameters
where Name LIKE '\r'

这两个 SQL 查询都是有效的，但并没有返回我需要的结果。我需要使用 Like 命令还是其他命令？如何将回车符加入查询中？

回车符不一定在行末（可能在中间）。

- Maestro1024

---

这是针对 SQL Server 的吗？ - KM.

Answer 1

这个方法会很慢，但如果只是一次性的需求，可以尝试...

select * from parameters where name like '%'+char(13)+'%' or name like '%'+char(10)+'%'

请注意，ANSI SQL 字符串连接运算符为 "||"，因此可能需要这样写：

select * from parameters where name like '%' || char(13) || '%' or name like '%' || char(10) || '%'

- KM.

---

PostgreSQL使用函数名chr而不是char。 - Clint Pachl

如果您查看编辑历史记录，您将看到“ANSI SQL”部分是添加到我的答案中的。我从未在我所做的任何 SQL 中使用过 ||。我从来不关心 ANSI SQL 的等效物。我只为当前的数据库编写代码并完成它。根据我的经验，您永远不会从一个数据库供应商转换到另一个供应商。每个供应商都添加了许多额外的SQL命令或变体，超越和/或替代了ANSI标准，这使得编写复杂的任何东西并且100％符合ANSI变得几乎不可能。 - KM.

|| 不是 OR，在 PostgreSQL 中是拼接符。 - jacktrade

@jipipayo，我不喜欢别人这样编辑我的答案。 - KM.

@KM，我没有编辑你的答案，那是Kevin Panko做的，我只是参考了上面mmcrae的评论。 - jacktrade

Answer 2

主要问题是删除回车符和换行符。我使用替换和字符函数来解决：

Select replace(replace(Name,char(10),''),char(13),'')

对于Postgres或Oracle SQL，请改用CHR函数：

       replace(replace(Name,CHR(10),''),CHR(13),'')

- Grant Dever

---

这个选择只会显示表格，没有回车和换行符，但是如何从源表中永久删除它？ - Daniel Horvath

Answer 3

在SQL Server中，我会使用：

WHERE CHARINDEX(CHAR(13), name) <> 0 OR CHARINDEX(CHAR(10), name) <> 0

这将搜索回车符和换行符。

如果你也想搜索制表符，只需添加：

OR CHARINDEX(CHAR(9), name) <> 0

- HLGEM

Answer 4

以下代码可用：

select * from table where column like '%（按回车键）%'

请忽略括号，并按回车键换行。

- Logan

---

这是所有发布的答案中唯一适用于我使用的PostgreSQL 9.6.19的解决方案。 - undefined

Answer 5

您还可以使用正则表达式：

SELECT * FROM Parameters WHERE Name REGEXP '\n';

- JYelton

Answer 6

你可以创建一个函数：

CREATE FUNCTION dbo.[Check_existance_of_carriage_return_line_feed]
(
      @String VARCHAR(MAX)
)
RETURNS VARCHAR(MAX)
BEGIN
DECLARE @RETURN_BOOLEAN INT

;WITH N1 (n) AS (SELECT 1 UNION ALL SELECT 1),
N2 (n) AS (SELECT 1 FROM N1 AS X, N1 AS Y),
N3 (n) AS (SELECT 1 FROM N2 AS X, N2 AS Y),
N4 (n) AS (SELECT ROW_NUMBER() OVER(ORDER BY X.n)
FROM N3 AS X, N3 AS Y)

SELECT @RETURN_BOOLEAN =COUNT(*)
FROM N4 Nums
WHERE Nums.n<=LEN(@String) AND ASCII(SUBSTRING(@String,Nums.n,1)) 
IN (13,10)    

RETURN (CASE WHEN @RETURN_BOOLEAN >0 THEN 'TRUE' ELSE 'FALSE' END)
END
GO

然后，您可以简单地运行像这样的查询：

SELECT column_name, dbo.[Check_existance_of_carriage_return_line_feed] (column_name)
AS [Boolean]
FROM [table_name]

- Amit Tikoo

Answer 7

对我来说，类似这样的东西似乎可以工作：

SELECT * FROM Parameters WHERE Name LIKE '%\n%'

- djc

Answer 8

这也可以工作

SELECT TRANSLATE(STRING_WITH_NL_CR, CHAR(10) || CHAR(13), '  ') FROM DUAL;

- user2394206

---

这是Oracle语法吗？ - HLGEM

Answer 9

从你的第一个查询中省略双引号。

... LIKE '%\n%' 

- Byron Whitlock

Answer 10

如果您正在考虑创建一个函数，请尝试以下方法： DECLARE @schema sysname = 'dbo' , @tablename sysname = 'mvtEST' , @cmd NVarchar(2000) , @ColName sysname

    DECLARE @NewLine Table
    (ColumnName Varchar(100)
    ,Location Int
    ,ColumnValue Varchar(8000)
    )

    SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE  TABLE_SCHEMA = @schema AND TABLE_NAME =  @tablename AND DATA_TYPE LIKE '%CHAR%'

    DECLARE looper CURSOR FAST_FORWARD for
    SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = @schema AND TABLE_NAME =  @tablename AND DATA_TYPE LIKE '%CHAR%'
    OPEN looper
    FETCH NEXT FROM looper INTO @ColName

    WHILE @@fetch_status = 0
    BEGIN
    SELECT @cmd = 'select ''' +@ColName+    ''',  CHARINDEX(Char(10),  '+  @ColName +') , '+ @ColName + ' from '+@schema + '.'+@tablename +' where CHARINDEX(Char(10),  '+  @ColName +' ) > 0 or CHARINDEX(CHAR(13), '+@ColName +') > 0'
    PRINT @cmd
    INSERT @NewLine ( ColumnName, Location, ColumnValue )
    EXEC sp_executesql @cmd
    FETCH NEXT FROM looper INTO @ColName
    end
    CLOSE looper
    DEALLOCATE looper


    SELECT * FROM  @NewLine

- Habib