regexp_extract grammar :

regexp_extract(string subject, string pattern, int index)

Return value : string

explain ： The string subject according to pattern Rule splitting of regular expressions , return index Specified characters .

The first parameter ： Fields to process

The second parameter : A regular expression to match

The third parameter :

0 Is to display the entire string that matches it
1 Is to show the first bracket inside
2 Is to display the fields in the second bracket …

Be careful , In some cases, escape characters are used （ Double slash ‘\’）.

 select
regexp_extract('x=a3&x=18abc&x=2&y=3&x=4','x=([0-9]+)([a-z]+)',0), -- x=18abc
regexp_extract('x=a3&x=18abc&x=2&y=3&x=4','^x=([a-z]+)([0-9]+)',0), -- x=a3
regexp_extract('https://detail.tmall.com/item.htm?spm=608.7065813.ne.1.Ni3rsN&id=522228774076&tracelog=fromnonactive','id=([0-9]+)',0), -- id=522228774076
regexp_extract('https://detail.tmall.com/item.htm?spm=608.7065813.ne.1.Ni3rsN&id=522228774076&tracelog=fromnonactive','id=([0-9]+)',1), -- 522228774076
regexp_extract('http://a.m.taobao.com/i41915173660.htm','i([0-9]+)',0), -- i41915173660
regexp_extract('http://a.m.taobao.com/i41915173660.htm','i([0-9]+)',1) -- 41915173660
from test.dual;

Regular matching is common

sketch ：

expression .* It's easy to understand , It's a single character matching any number of times , Greedy matching .
expression .*? If the condition is met, it will only match once , Lazy matching

Example

expression .* It's easy to understand , It's a single character matching any number of times , Greedy matching . Regular expressions (.?) Inertia matching
1、. Match any line break “\n” Characters outside ;
2、 Represents a match to the previous character 0 Times or infinite times ;
3、+ or Heel ？ Greedy means no match , That is, match as little as possible , Such as ？ Repeat any number of times , But repeat as little as possible ;
4、 .? Represents matching any number of repetitions , But use the least amount of repetition if you can make the whole match successful .
Such as ：a.?b Match the shortest , With a Start , With b Ending string . If you apply it to aabab Words , It will match aab and ab.

Common applications

^ Beginning of expression
$ End of expression
. For any character
* Denotes any number of

Reference link ：https://www.cnblogs.com/skyEva/p/5175377.html