Oracle SQL：使用 regexp_substr 返回字符串的第一行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17652730/
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
Oracle SQL: Return first line of string using regexp_substr
提问by bawpie
I am trying to return the first line of text from a text box in an SQL query (oracle 11). The content of the text box looks like this:
我正在尝试从 SQL 查询（oracle 11）中的文本框中返回第一行文本。文本框的内容如下所示：
X WITHDRAWN
Explanation.
I want to return the top line, i.e. the X WITHDRAWN. I'm not sure if I can specify to look at the first line only, or to just return all text before a carriage return - either would work.
我想返回顶行，即 X WITHDRAWN。我不确定我是否可以指定只查看第一行，或者只在回车之前返回所有文本 - 两者都可以。
I think I need to use regexp_substr but I'm not quite sure on the syntax. I have tried:
我想我需要使用 regexp_substr 但我不太确定语法。我试过了：
regexp_substr(TABLE.TEXT,'^.*$')
but it didn't work, so any assistance would be much appreciated!
但它没有用，所以任何帮助将不胜感激！
EDIT: The solution used:
编辑：使用的解决方案：
select regexp_substr(TABLE.TEXT, '[^,]+['||CHR(10)||']') from tab
EDIT: I noticed I was getting a mixture of line feed and carriage returns returned in my answer, so I've use the following solution to return just the text and no additional characters.
编辑：我注意到我的答案中混合了换行符和回车符，所以我使用以下解决方案只返回文本而不返回其他字符。
select
replace(replace(regexp_substr(TABLE.TEXT, '[^,]+['||CHR(10)||']'),CHR(10),''),CHR(13),'')
from tab
EDIT: Following @Ben's answer, I've amended my solution to the following:
编辑：按照@Ben 的回答，我已将我的解决方案修改为以下内容：
select
initcap(replace(regexp_substr(TABLE.TEXT, '.*$', 1, 1, 'm'),CHR(13),''))
from tab
回答by Ben
Parado's regular expression matches everything that's not a comma multiple times followed by a carriage return. This means it won't work for a line-feed or if there's a comma in the text.
Parado 的正则表达式多次匹配不是逗号的所有内容，然后是回车符。这意味着它不适用于换行或文本中有逗号。
Oracle supports multi-line expressions using the mmatch parameter. When using this mode, $matches the end of each line as well as the end of the string. You can use this to simply the expression massively to:
Oracle 支持使用mmatch 参数的多行表达式。使用此模式时，$匹配每行的结尾以及字符串的结尾。您可以使用它来大量简化表达式：
regexp_substr(str, '.*$', 1, 1, 'm')
That is match the first occurrence (the first line) of the string that matches anything, followed by the end of the string, counting from the first character.
即匹配匹配任何内容的字符串的第一次出现（第一行），然后是字符串的结尾，从第一个字符开始计数。
As an example:
举个例子：
with strings as (
select 'hi
hi again' as str
from dual
union all
select 'bye
and again'
from dual
)
select regexp_substr(str, '.*$', 1, 1, 'm')
from strings
回答by Dmitry Bychenko
You may try using direct substring (with carriage return - Chr(10) - being symbol to find) instead of regular expressions:
您可以尝试使用直接子字符串（带有回车符 - Chr(10) - 是要查找的符号）而不是正则表达式：
SubStr(Table.Text, 1, decode(InStr(Table.Text, Chr(10)), 0, Length(Table.Text), InStr(Table.Text, Chr(10))) - 1)
