Oracle-从字符串中提取多个子字符串

本文介绍了Oracle-从字符串中提取多个子字符串的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我需要提取在单个字符串上提到的所有电子邮件帐户，到目前为止，我已经尝试使用SUBSTR和INSTR，但是没有成功，这里是一个示例:

I need to extract all e-mail accounts mentioned on a single string, so far I've tried with SUBSTR and INSTR, but with no success, here an example:

字符串看起来像这样:

()
string = "User_1" {[email protected]};"User_2" {[email protected]};"User_3" {[email protected]};"User_4" {[email protected]};


select SUBSTR(string ,INSTR(string ,'<',-1,2)) EMAIL
from dual;

我需要的是这样的东西:

What I need is something like this:

[email protected];[email protected];[email protected];[email protected];

推荐答案

可以在字符串中查找前面的{和下面的};，可以使用:

Looking for the preceding { and the following }; which seems to appear in your string you can use:

SELECT REGEXP_REPLACE(
         '"User_1" {[email protected]};"User_2" {[email protected]};"User_3" {[email protected]};"User_4" {[email protected]};',
         '.*?\{(.*?)\};',
         '\1;'
       ) AS emails
FROM   DUAL;

输出:

EMAILS                                                                
------------------------------------------------------------------------
[email protected];[email protected];[email protected];[email protected];

这篇关于Oracle-从字符串中提取多个子字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！

符串