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
How to search new line char in oracle table?
提问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));