Oracle SQL:使用regexp_substr返回字符串的第一行 [英] Oracle SQL: Return first line of string using regexp_substr

问题描述

我正在尝试从SQL查询(oracle 11)的文本框中返回文本的第一行.文本框的内容如下:

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:

   X WITHDRAWN

   Explanation.

我想返回第一行，即X WITHDRAWN.我不确定是否可以指定只看第一行，还是只在回车之前返回所有文本-两种方法都可以.

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.

我认为我需要使用regexp_substr，但是我不太确定语法.我已经尝试过:

I think I need to use regexp_substr but I'm not quite sure on the syntax. I have tried:

   regexp_substr(TABLE.TEXT,'^.*$')

但是它没有用，所以任何帮助将不胜感激！

but it didn't work, so any assistance would be much appreciated!

使用的解决方案:

   select regexp_substr(TABLE.TEXT, '[^,]+['||CHR(10)||']') from tab

我注意到我得到的答案中包含换行符和回车符的混合，所以我使用以下解决方案仅返回文本，不返回其他字符.

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 

按照@Ben的回答，我将解决方案修改为以下内容:

Following @Ben's answer, I've amended my solution to the following:

select
initcap(replace(regexp_substr(TABLE.TEXT, '.*$', 1, 1, 'm'),CHR(13),''))
from tab

推荐答案

Parado的正则表达式多次匹配不是逗号的所有内容，然后返回回车符.这意味着它不能用于换行或文本中有逗号.

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.

Oracle使用m match参数来支持多行表达式.使用此模式时，$匹配每行的结尾和字符串的结尾.您可以使用它简单地将表达式大规模地应用于:

Oracle supports multi-line expressions using the m match 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:

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.

例如:

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

这篇关于Oracle SQL:使用regexp_substr返回字符串的第一行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！