SQL 如何在oracle表中搜索新行字符?

声明:本页面是StackOverFlow热门问题的中英对照翻译,遵循CC BY-SA 4.0协议,如果您需要使用它,必须同样遵循CC BY-SA许可,注明原文地址和作者信息,同时你必须将它归于原作者(不是我):StackOverFlow 原文地址: http://stackoverflow.com/questions/13482235/
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

提示:将鼠标放在中文语句上可以显示对应的英文。显示中英文
时间:2020-09-01 12:13:40  来源:igfitidea点击:

How to search new line char in oracle table?

sqloraclenewline

提问by meilechh

I have an issue with a file that's created from data (in format of bytes) returned from the database.
the problem is that there is a few newlines in the file.
i would like to know if there is a way to write a where clause to check if some record has a newline character?

我有一个从数据库返回的数据(以字节格式)创建的文件的问题。
问题是文件中有几个换行符。
我想知道是否有办法编写 where 子句来检查某些记录是否有换行符?

回答by APC

Using the CHR function to look for the ASCII value:

使用 CHR 函数查找 ASCII 值:

select *
from your_table
where instr(your_text_col, chr(10)) > 0;

If you want to search for carriage returns, that would be chr(13).

如果你想搜索回车,那就是 chr(13)。

回答by ruakh

You can write something like:

你可以这样写:

SELECT id
  FROM table_name
 WHERE field_name LIKE '%'||CHR(10)||'%'
;

(||is the concatenation operator; CHR(10)is the tenth ASCII character, i.e. a newline.)

||是连接运算符;CHR(10)是第十个 ASCII 字符,即换行符。)

回答by Justin Cave

Depending on the platform, a newline will generally either be a CHR(10)(Unix) or a CHR(13)followed by a CHR(10)(Windows). There are other options for other more esoteric platforms, but 99.999% of the time, it will be one of these two.

根据平台的不同,换行符通常是 a CHR(10)(Unix) 或 aCHR(13)后跟 a CHR(10)(Windows)。其他更深奥的平台还有其他选择,但在 99.999% 的情况下,它将是这两个平台之一。

You can search the data in a column looking for one or both characters

您可以搜索一列中的数据以查找一个或两个字符

SELECT instr( column_name, CHR(10) ) position_of_first_lf,
       instr( column_name, CHR(13) || CHR(10) ) position_of_first_cr_lf
  FROM table_name
 WHERE instr( column_name, CHR(10) ) > 0

回答by Anil

This worked the best for me.

这对我来说效果最好。

select * from label_master where regexp_like (text, chr(10));

select * from label_master where regexp_like (text, chr(10));