oracle regular expression extracting string inside tags

Question

This is for my practice, I've a text like:

 "lovely heart"<[email protected]>,
 "<<*>>Freeeky<<*>> Jack" <[email protected]>,
 "heavens's kingk*ng '-'asdf" <[email protected]>
 "sample[^-^]"<[email protected]>

I need to extract only:

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

Here's my try, but still its half or less done.

WITH t AS
     (SELECT '"lovely heart"<[email protected]>,
"<<*>>Freeeky<<*>> Jack" <[email protected]>, 
"heavens''s kingk*ng ''-''asdf" <[email protected]>' word
     FROM dual
     )
SELECT regexp_substr(word, '<(.*@.*)>',1,LEVEL, NULL,1)
FROM t
     CONNECT BY level <= regexp_count(word, '<(.*@.*)>');

some results are like:

<*>>Freeeky<<*>> Jack" <[email protected]

Any good solution please.

Thanks

What do you mean by half done? Didn't this give you expected result? — Rohit Jain
– Rohit Jain, Commented Aug 2, 2013 at 10:17

Rohit Jain · Accepted Answer · 2013-08-02 10:29:31Z

2

The problem with your regex is, the first .* after < will match all characters before the @, as a dot(.) in regex can match any character except the newline. So, it would even match < and >. Here's how it matches your string:

'"< <<*>>Freeeky<<*>> Jack" <[email protected]  >"'
  ^ ^                                       ^  ^
  | -----------------------------------------  |
  |                      |                     |
 Match the first `<`   (.*@.*)           Match the last `>`.

So, the captured group is:

<<*>>Freeeky<<*>> Jack" <[email protected]

Which is that you got. You can change .* to [^<>]* to match any characters except < and >:

Use the following regex:

'<([^<>]*@[^<>]*)>'

edited Aug 2, 2013 at 10:29

answered Aug 2, 2013 at 10:24

Rohit Jain

214k45 gold badges419 silver badges534 bronze badges

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

8 Comments

ajmalmhd04 Over a year ago

And one more question, I've manually added the extra quotes for the single quotes for the text, Is there other way to use only in one query?

Rohit Jain Over a year ago

@ajmalmhd04. I am afraid, I can't answer that. Have no experience in Oracle DB. Sorry for that.

ajmalmhd04 Over a year ago

Okay, no problem, Let me swim deep in to regexp and wait for others answers.

Rohit Jain Over a year ago

@ajmalmhd04. I guess that is the correct way to escape single quotes. See this post

ajmalmhd04 Over a year ago

Yeah, agree, but how would I know if I'm only having the text and use the query directly for a large data?!

|

Collectives™ on Stack Overflow

oracle regular expression extracting string inside tags

1 Answer 1

8 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related