正则表达式在Redshift中返回字符串中所有位于” at”之前的单词。

huangapple go评论50阅读模式
英文:

Regular expression in Redshift that returns all words prior to " at" in a string

问题

使用 Redshift 中的 REGEX_SUBSTR() 函数,我想要搜索一个字符串并提取在字符串中位于 " at" 之前的所有单词。关键点是在 "at" 前的空格。

regex tester 上,我测试了 [^.]*(?=\sat),它有效。

但在 Redshift 中,我一直收到无法解释正则表达式的错误。始终出现以下错误:

error:  Invalid preceding regular expression prior to repetition operator.  The error occurred while parsing the regular expression: '[^.]*(>>>HERE>>>=\sat)'.
  code:      8002
  context:   T_regexp_init
  query:     0
  location:  sql_regex_funcs.hpp:175

在理想情况下,运行以下 SQL 语句:

SELECT regexp_substr('withdrawal from Credit Union at main avenue', '[^.]*(?=\\sat)')

将返回 withdrawal from Credit Union

英文:

Using REGEX_SUBSTR() in redshift I want to search a string and extract all words that precede " at" in a string. the space before the at is key.

I tested [^.]*(?=\sat) on regex tester it works.

But inside Redshift I continually get errors that the regex expression cannot be interpreted. Consistently getting this error

I've tried using double backslash (\\s) but no luck with that either. For some reason inside the positive lookahead assertion Redshift is not recognizing any escape characters.

error:  Invalid preceding regular expression prior to repetition operator.  The error occurred while parsing the regular expression: '[^.]*(?>>>HERE>>>=\sat)'.
  code:      8002
  context:   T_regexp_init
  query:     0
  location:  sql_regex_funcs.hpp:175

In a perfect world running

SELECT regexp_substr('withdrawal from Credit Union at main avenue', '[^.]*(?=\\sat)')

would return withdrawal from Credit Union

答案1

得分: 1

你可以使用以下代码:

SELECT regexp_substr('withdrawal from Credit Union at main avenue', '([^.]*)\\sat', 1, 1, 'e')

在这里,

  • ([^.]*)\sat 匹配零或多个除 . 之外的字符,尽可能多次出现直到空格和 at 子字符串
  • 1, 1, 'e' 意味着搜索从输入字符串的第一个字符开始,第一个匹配项将被返回,返回的子字符串将是第一个捕获组的值,即与第一个和唯一带括号的正则表达式部分匹配的内容,即 ([^.]*)
英文:

You can use

SELECT regexp_substr('withdrawal from Credit Union at main avenue', '([^.]*)\\sat', 1, 1, 'e')

Here,

  • ([^.]*)\sat matches zero or more chars other than . as many times as possible up to a whitespace and at substring
  • 1, 1, 'e' mean that the search starts at the first character in the input string, the first occurrence will be returned and the substring returned will be the first capturing group value, i.e. what was matched with the first and only parenthesized regex part, ([^.]*).

huangapple
  • 本文由 发表于 2023年4月7日 00:40:06
  • 转载请务必保留本文链接:https://go.coder-hub.com/75951849.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定