英文:
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 andat
substring1, 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,([^.]*)
.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论