Oracle SQL：在两个字符之间提取文本
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/50004535/
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: Extracting text between two characters
提问by Anish
I have strings that look like
我有看起来像的字符串
{ABCDE}{F1}
{GHIJ}{K12}
I want to extract the text between the first curly brackets
我想提取第一个大括号之间的文本
ABCDE
GHIJ
I tried searching online but a lot of answers seem to be using functions or PL-SQL. Can someone please help me with this?
我尝试在线搜索，但很多答案似乎都在使用函数或 PL-SQL。有人可以帮我解决这个问题吗？
回答by Tim Biegeleisen
We can use REGEXP_SUBSTRhere:
我们可以REGEXP_SUBSTR在这里使用：
SELECT regexp_substr('{ABCDE}{F1}', '\{([^}]+)\}', 1,1,NULL,1) AS output
FROM dual
Demo
演示
This is a less commonly seen use of REGEXP_SUBSTR, which uses a capture group, per this pattern:
这是一个不太常见的 使用REGEXP_SUBSTR，它使用一个捕获组，按照这个模式：
\{([^}]+)\}
The sixth parameter says to return the first capture group.
第六个参数表示返回第一个捕获组。
回答by Christian Palmer
This is old skool. I love regular expressions and can understand them but get into an awful lot of trouble when I try to produce them. A bit like Spanish (for me). So this is just SQL INSTR / SUBSTR / REPLACE. I'm not expecting any upvotes...
这是老skool。我喜欢正则表达式并且可以理解它们，但是当我尝试生成它们时遇到了很多麻烦。有点像西班牙语（对我来说）。所以这只是 SQL INSTR / SUBSTR / REPLACE。我不期待任何赞成...
WITH test_data (raw_text)
AS
(SELECT '{ABCDE}{F1}' from dual UNION ALL
SELECT '{GHIJ}{K12}' from dual
)
SELECT
raw_text
,SUBSTR(raw_text,2,INSTR(raw_text,'}{')-2) first_string
,REPLACE(SUBSTR(raw_text,INSTR(raw_text,'}{')+2),'}') second_string
--these two from Tim's excellent answer
,regexp_substr(raw_text, '\{([^}]+)\}', 1,1,NULL,1)
,regexp_substr(raw_text, '\{([^}]+)\}', 1,2,NULL,1)
FROM
test_data
;
Demo
演示
回答by David Faber
You might try something like the following:
您可以尝试以下操作：
SELECT TRIM( '{' FROM REGEXP_SUBSTR(mystring, '\{[^}]+') )
FROM mytable;
This will capture the text from the first opening curly brace up to but not including the closing curly brace, then trim the opening curly brace from the result.
这将捕获从第一个左花括号到但不包括右花括号的文本，然后从结果中修剪左花括号。
Hope this helps.
希望这可以帮助。
