SQL 查询字符串中的回车并最终删除回车
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1337196/
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
SQL query for a carriage return in a string and ultimately removing carriage return
提问by Maestro1024
SQL query for a carriage return in a string and ultimately removing carriage return
SQL 查询字符串中的回车并最终删除回车
I have some data in a table and there are some carriage returns in places where I don't want them. I am trying to write a query to get all of the strings that contain carriage returns.
我在表中有一些数据，并且在我不想要它们的地方有一些回车。我正在尝试编写一个查询来获取所有包含回车的字符串。
I tried this
我试过这个
select * from Parameters
where Name LIKE '%"\n" %'
Also
还
select * from Parameters
where Name LIKE '\r'
'
'
Both are valid SQL but are not returning what I am looking for. Do I need to use the Likecommand or a different command? How do I get the carriage return into the query?
两者都是有效的 SQL，但没有返回我正在寻找的内容。我需要使用Like命令还是其他命令？如何将回车符放入查询中？
The carriage return is not necessarily at the end of the line either (may be in the middle).
回车也不一定在行尾（可能在中间）。
回答by KM.
this will be slow, but if it is a one time thing, try...
这会很慢，但如果这是一次性的，请尝试......
select * from parameters where name like '%'+char(13)+'%' or name like '%'+char(10)+'%'
Note that the ANSI SQL string concatenation operator is "||", so it may need to be:
请注意，ANSI SQL 字符串连接运算符是“||”，因此可能需要：
select * from parameters where name like '%' || char(13) || '%' or name like '%' || char(10) || '%'
回答by Grant Dever
The main question was to remove the CR/LF. Using the replace and char functions works for me:
主要问题是删除 CR/LF。使用 replace 和 char 函数对我有用：
Select replace(replace(Name,char(10),''),char(13),'')
For Postgres or Oracle SQL, use the CHR function instead:
对于 Postgres 或 Oracle SQL，请改用 CHR 函数：
replace(replace(Name,CHR(10),''),CHR(13),'')
回答by HLGEM
In SQL Server I would use:
在 SQL Server 中，我会使用：
WHERE CHARINDEX(CHAR(13), name) <> 0 OR CHARINDEX(CHAR(10), name) <> 0
This will search for both carriage returnsand line feeds.
这将搜索回车和换行。
If you want to search for tabstoo just add:
如果您也想搜索标签，只需添加：
OR CHARINDEX(CHAR(9), name) <> 0
回答by JYelton
You can also use regular expressions:
您还可以使用正则表达式：
SELECT * FROM Parameters WHERE Name REGEXP '\n';
回答by Logan
this works: select * from table where column like '%(hit enter)%'
这有效： select * from table where column like '%(hit enter)%'
Ignore the brackets and hit enter to introduce new line.
忽略括号并按 Enter 键以引入新行。
回答by Amit Tikoo
You can create a function:
您可以创建一个函数：
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
Then you can simple run a query like this:
然后你可以简单地运行一个这样的查询：
SELECT column_name, dbo.[Check_existance_of_carriage_return_line_feed] (column_name)
AS [Boolean]
FROM [table_name]
回答by Habib
If you are considering creating a function, try this: DECLARE @schema sysname = 'dbo' , @tablename sysname = 'mvtEST' , @cmd NVarchar(2000) , @ColName sysname
如果你正在考虑创建一个函数，试试这个： 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
回答by user2394206
This also works
这也有效
SELECT TRANSLATE(STRING_WITH_NL_CR, CHAR(10) || CHAR(13), ' ') FROM DUAL;
回答by Byron Whitlock
Omit the double quotes from your first query.
省略第一个查询中的双引号。
... LIKE '%\n%'
回答by djc
Something like this seems to work for me:
这样的事情似乎对我有用：
SELECT * FROM Parameters WHERE Name LIKE '%\n%'
