Use connect by by in REGEXP_SUBSTR without breaking result to multiple rows

Question

SELECT CHR(91)||'a-zA-Z0-9._%-'||CHR(93)||'+'|| listagg(REGEXP_SUBSTR('[email protected], [email protected]', '@'||CHR(91)||'^,'||CHR(93)||'+', 1, LEVEL), ', ') within group (order by level) as domain
FROM DUAL
CONNECT BY REGEXP_SUBSTR('[email protected], [email protected]','@'||CHR(91)||'^,'||CHR(93)||'+', 1, LEVEL) IS NOT NULL
order by 1;

Above script only has the regular expression in front of @yahoo.com

 [a-zA-Z0-9._%-][email protected], @hotmail.com

Expected result:

[a-zA-Z0-9._%-][email protected], [a-zA-Z0-9._%-][email protected]

Littlefoot · Accepted Answer · 2021-04-08 10:36:09Z

0

Sure; aggregate them back.

SQL> SELECT listagg(REGEXP_SUBSTR('[email protected], [email protected]', '@'||CHR(91)||'^,'||CHR(93)||'+', 1, LEVEL), ', ') within group (order by level) as domain
  2  FROM DUAL
  3  CONNECT BY REGEXP_SUBSTR('[email protected], [email protected]','@'||CHR(91)||'^,'||CHR(93)||'+', 1, LEVEL) IS NOT NULL
  4  order by 1;

DOMAIN
----------------------------------------------------------------------------------------------------
@yahoo.com, @hotmail.com

SQL>

If you want to put the regexp prefix to all domains, then

SQL>     SELECT LISTAGG (   '[a-zA-Z0-9._%-]+'
  2                      || REGEXP_SUBSTR ('[email protected], [email protected]',
  3                                        '@' || CHR (91) || '^,' || CHR (93) || '+',
  4                                        1,
  5                                        LEVEL),
  6                      ', ')
  7             WITHIN GROUP (ORDER BY LEVEL) AS domain
  8        FROM DUAL
  9  CONNECT BY REGEXP_SUBSTR ('[email protected], [email protected]',
 10                            '@' || CHR (91) || '^,' || CHR (93) || '+',
 11                            1,
 12                            LEVEL)
 13                IS NOT NULL;

DOMAIN
-----------------------------------------------------------------------------------------------
[a-zA-Z0-9._%-][email protected], [a-zA-Z0-9._%-][email protected]

SQL>

edited Apr 8, 2021 at 10:36

answered Apr 8, 2021 at 10:02

Littlefoot

144k15 gold badges41 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

user2102665 Over a year ago

Thanks, it works. How if I need both domains prefixed a reg exp.. Currently only the first entry did.[a-zA-Z0-9._%-][email protected], @hotmail.com

Littlefoot Over a year ago

I'm afraid I don't understand the question. Please, edit your initial post and add sample data, as well as desired output.

Littlefoot Over a year ago

I edited the answer and added some more code. Have a look.

Collectives™ on Stack Overflow

Use connect by by in REGEXP_SUBSTR without breaking result to multiple rows

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related