PostgreSQL 正则表达式替换-使用变量方式

alert( 'AAAA' . replace (/([A]{1})/g, "$1-" ));

select regexp_replace( 'AAAAAAAAAAAAAAAAAAAAAA' , '([A-Z]{1})' , '\1-' , 'g' )

select f1[1] from regexp_matches( 'asdfadfadsf{_id}' , '[\{]{1}(.*?)[\}]{1}' ) as f1

-- \1表示只匹配第一个子串

select regexp_replace( 'adsfjjbkk中中' , '(.)(\1)+' , '\1' , 'g' )

select regexp_replace( 'adaaasfjjjbkk' , '(.).*(\1)+' , '\1' , 'g' )

select substring ( 'abc123d4' from '^(.*?)..$' );

- output abc123

select * from regexp_matches( ' abc123d 4测试 ' , '[^ ]+' , 'g' );

select * from regexp_matches( ' abc123d4测试 ' , '[^ ]+' , 'g' );

- output abc123

select regexp_replace( ' abc123d4 测试 ' , '^[ ]?(.*?)[ ]?$' , '\1' , 'g' );

select f1 from regexp_split_to_table( '<div id="u1"><a href="http://news.baidu.com" rel="external nofollow" name="tj_trnews" class="mnav">新闻</a><a href="https://www.hao123.com" rel="external nofollow" name="tj_trhao123" class="mnav">hao123</a><a href="http://map.baidu.com" rel="external nofollow" name="tj_trmap" class="mnav">地图</a><a href="http://v.baidu.com" rel="external nofollow" name="tj_trvideo" class="mnav">视频</a><a href="http://tieba.baidu.com" rel="external nofollow" name="tj_trtieba" class="mnav">贴吧</a><a href="http://xueshu.baidu.com" rel="external nofollow" name="tj_trxueshu" class="mnav">学术</a><a href="https://passport.baidu.com/v2/?login&amp;tpl=mn&amp;u=http%3A%2F%2Fwww.baidu.com%2F&amp;sms=5" rel="external nofollow" name="tj_login" class="lb" onclick="return false;">登录</a><a href="http://www.baidu.com/gaoji/preferences.html" rel="external nofollow" name="tj_settingicon" class="pf">设置</a><a href="http://www.baidu.com/more/" rel="external nofollow" name="tj_briicon" class="bri" style="display: block;">更多产品</a></div>' , '<[^>]*>' ) as f1 where f1<> ''

with cte as (

  select '实际月份少一个月a1.' as f limit 1

) select (regexp_matches(f, '^(.{4})' ))[1] as start,(regexp_matches(f, '(.{4})$' ))[1] as end from cte

select array_agg(vals[1]),array_agg(vals[2]) from regexp_matches( '字段1:值1,字段2:,:3,字段4:4' , '(\w+)?[:]{1}(\w+)?,?' , 'g' ) as vals;

select array_agg(vals[1]),array_agg(vals[2]) from regexp_matches( '字段1=值1,字段2=,=3,字段4=4' , '(\w+)?[=]{1}(\w+)?,?' , 'g' ) as vals;

--正向匹配,连继的3个字母右边不能出现:

select * from regexp_matches( 'asf:::::' , '[a-zA-Z]{3}(?!:)' ) as f1

--反向匹配,连继的3个字母左边不能出现:

select * from regexp_matches( ':::::asdf' , '(?<!:)[a-zA-Z]{3}' ) as f1

--仅整数

select * from test where name !~ '[0-9]+'

--高大上的写法,包含带符号的整数和浮点数及科学计数

select * from test where name !~ '[-+]?[0-9]*\.?[0-9]+([eE][-+]?[0-9]+)?'

select '(0871)68111111' ~ '^(\([0-9]{3,4}\)|[0-9]{3,4}-)?[0-9]{7,8}$'

/*

接受以下格式

(0871)7位或8位电话号码

(871)7位或8位电话号码

0871-7位或8位电话号码

871-7位或8位电话号码

*/

select '+8613000000000' ~ '^((\+86)|(86)|(\(\+86\))|(\(86\))|(（\+86）)|(（86）))?(1[0-9]{10})$' ,

'8613000000000' ~ '^((\+86)|(86)|(\(\+86\))|(\(86\))|(（\+86）)|(（86）))?(1[0-9]{10})$' ,

'(+86)13000000000' ~ '^((\+86)|(86)|(\(\+86\))|(\(86\))|(（\+86）)|(（86）))?(1[0-9]{10})$' ,

'(86)13000000000' ~ '^((\+86)|(86)|(\(\+86\))|(\(86\))|(（\+86）)|(（86）))?(1[0-9]{10})$' ,

'（+86）13000000000' ~ '^((\+86)|(86)|(\(\+86\))|(\(86\))|(（\+86）)|(（86）))?(1[0-9]{10})$' ,

'（86）13000000000' ~ '^((\+86)|(86)|(\(\+86\))|(\(86\))|(（\+86）)|(（86）))?(1[0-9]{10})$' ,

'13000000000' ~ '^((\+86)|(86)|(\(\+86\))|(\(86\))|(（\+86）)|(（86）))?(1[0-9]{10})$'

--提取移动电话

select tmp[8] from regexp_matches( '（+86）13000000000' , '^((\+86)|(86)|(\(\+86\))|(\(86\))|(（\+86）)|(（86）))?(1[0-9]{10})$' , 'g' ) as tmp

constraint ck_users_uname check (uname~ '^[0-9a-zA-Z_]{6,16}$' and uname !~ '^[0-9]+$' ),

--同时匹配两个字一次,不能用中括号[]

select regexp_replace( '打麻将出老千速度太快将速' , '((麻将)|(速度){1})' , '<strong>\1</strong>' , 'g' )

--不正确的写法,请注意看它们之间的区别

select regexp_replace( '打麻将出老千速度太快将速' , '([麻将|速度]{2})' , '<strong>\1</strong>' , 'g' )

select regexp_replace( '打麻将出老千速度太快将速' , '([麻将|速度]{1})' , '<strong>\1</strong>' , 'g' )

with split as (

  select cast (deg[1] as integer ) as du,

  cast (deg[2] as integer ) as fen ,

  cast (deg[3] as integer ) as miao,

  deg[4] as direction,

  1000000 as f1

  from ( select regexp_matches( '984759' , '([-+]?[0-9]{1,3})[°]?([0-9]{2})[′]?([0-9]{2})[″]?([SNEW]?)' ) as deg) as tmp

),cte as (

  select

  ( case when du<0 or 'S' =direction or 'W' =direction then -1 else 1 end ) as sign,

  abs (du) * f1 as du,

  fen * f1 as fen,

  miao * f1 as miao,

  cast (f1 as float8) as f1

  from split

) select ((du + fen/60 + miao/3600) * sign) / f1 from cte;

select * from regexp_matches( 'Kmblack123456' , '^(?=.*[0-9]+)(?=.*[A-Z]+)(?=.*[a-z]+)[0-9a-zA-Z_@]{6,14}$' ) as f1

select * from regexp_matches( '1Kmblack123456' , '^(?![0-9]+)(?=.*[0-9]+)(?=.*[A-Z]+)(?=.*[a-z]+)[0-9a-zA-Z_@]{6,14}$' ) as f1

select * from regexp_matches( 'Kmblack123456' , '^(?![0-9]+)(?=.*[0-9]+)(?=.*[A-Z]+)(?=.*[a-z]+)[0-9a-zA-Z_@]{6,14}$' ) as f1

select * from regexp_matches( '2100-01-02T01:02:03Z' , '^((((19|20|21)[0-9]{2})-(0[1-9]|1[012])-(0[1-9]|[12][0-9]|3[01]))T((?:(?:([01]?[0-9]|2[0-3]):)?([0-5]?[0-9]):)?([0-5]?[0-9]))Z)$' ) as f1

select * from regexp_matches( '2100-01-02 01:02:03' , '^((((19|20|21)[0-9]{2})-(0[1-9]|1[012])-(0[1-9]|[12][0-9]|3[01]))\s((?:(?:([01]?[0-9]|2[0-3]):)?([0-5]?[0-9]):)?([0-5]?[0-9])))$' ) as f1

select regexp_replace( '所以 font-face 规则实际上是在找到:glyphicons地方' '声明? font-family 和位置' , '([\?' ':]{1})' , '?\1' , 'g' )

select 'a123' ~ '^((?![0-9_@]+)[0-9a-zA-Z_@]{4,16})$' ,

'0a123' ~ '^((?![0-9_@]+)[0-9a-zA-Z_@]{4,16})$' ,

'@a123' ~ '^((?![0-9_@]+)[0-9a-zA-Z_@]{4,16})$' ,

'_a123' ~ '^((?![0-9_@]+)[0-9a-zA-Z_@]{4,16})$' ,

'a1234567890123456' ~ '^((?![0-9_@]+)[0-9a-zA-Z_@]{4,16})$'

[root@localhost regex]# pwd

/opt/hgdb-core/src/include/regex

[root@localhost regex]# ll

total 40

-rw-r --r--. 1 postgres postgres 3490 Mar 19 19:00 regcustom.h

-rw-r --r--. 1 postgres postgres 1332 Mar 19 18:59 regerrs.h

-rw-r --r--. 1 postgres postgres 6703 Mar 19 19:00 regex.h

-rw-r --r--. 1 postgres postgres 2353 Mar 19 19:00 regexport.h

-rw-r --r--. 1 postgres postgres 16454 Mar 19 19:00 regguts.h

[root@localhost regex]# pwd

/opt/hgdb-core/src/backend/regex

[root@localhost regex]# ll reg*.c

-rw-r --r--. 1 postgres postgres 55851 Mar 19 19:00 regcomp.c

-rw-r --r--. 1 postgres postgres 3671 Mar 19 18:59 regerror.c

-rw-r --r--. 1 postgres postgres 34873 Mar 19 19:00 regexec.c

-rw-r --r--. 1 postgres postgres 2123 Mar 19 18:59 regfree.c

[root@localhost regex]#

[root@localhost adt]# pwd

/opt/hgdb-core/src/backend/utils/adt

[root@localhost adt]# ll regexp.c

-rw-r --r--. 1 postgres postgres 34863 Apr 12 02:29 regexp.c

[root@localhost adt]#

/* src/include/catalog/pg_proc.h */

DATA( insert OID = 2073 ( substring PGNSP PGUID 12 1 0 0 0 f f f f t f i 2 0 25 "25 25" _null_ _null_ _null_ _null_ _null_ textregexsubstr _null_ _null_ _null_ ));

DESCR( "extract text matching regular expression" );

DATA( insert OID = 2074 ( substring PGNSP PGUID 14 1 0 0 0 f f f f t f i 3 0 25 "25 25 25" _null_ _null_ _null_ _null_ _null_ "select pg_catalog.substring($1, pg_catalog.similar_escape($2, $3))" _null_ _null_ _null_ ));

DESCR( "extract text matching SQL99 regular expression" );

DATA( insert OID = 2284 ( regexp_replace PGNSP PGUID 12 1 0 0 0 f f f f t f i 3 0 25 "25 25 25" _null_ _null_ _null_ _null_ _null_ textregexreplace_noopt _null_ _null_ _null_ ));

DESCR( "replace text using regexp" );

DATA( insert OID = 2285 ( regexp_replace PGNSP PGUID 12 1 0 0 0 f f f f t f i 4 0 25 "25 25 25 25" _null_ _null_ _null_ _null_ _null_ textregexreplace _null_ _null_ _null_ ));

DESCR( "replace text using regexp" );

DATA( insert OID = 2763 ( regexp_matches PGNSP PGUID 12 1 1 0 0 f f f f t t i 2 0 1009 "25 25" _null_ _null_ _null_ _null_ _null_ regexp_matches_no_flags _null_ _null_ _null_ ));

DESCR( "find all match groups for regexp" );

DATA( insert OID = 2764 ( regexp_matches PGNSP PGUID 12 1 10 0 0 f f f f t t i 3 0 1009 "25 25 25" _null_ _null_ _null_ _null_ _null_ regexp_matches _null_ _null_ _null_ ));

DESCR( "find all match groups for regexp" );

DATA( insert OID = 2765 ( regexp_split_to_table PGNSP PGUID 12 1 1000 0 0 f f f f t t i 2 0 25 "25 25" _null_ _null_ _null_ _null_ _null_ regexp_split_to_table_no_flags _null_ _null_ _null_ ));

DESCR( "split string by pattern" );

DATA( insert OID = 2766 ( regexp_split_to_table PGNSP PGUID 12 1 1000 0 0 f f f f t t i 3 0 25 "25 25 25" _null_ _null_ _null_ _null_ _null_ regexp_split_to_table _null_ _null_ _null_ ));

DESCR( "split string by pattern" );

DATA( insert OID = 2767 ( regexp_split_to_array PGNSP PGUID 12 1 0 0 0 f f f f t f i 2 0 1009 "25 25" _null_ _null_ _null_ _null_ _null_ regexp_split_to_array_no_flags _null_ _null_ _null_ ));

DESCR( "split string by pattern" );

DATA( insert OID = 2768 ( regexp_split_to_array PGNSP PGUID 12 1 0 0 0 f f f f t f i 3 0 1009 "25 25 25" _null_ _null_ _null_ _null_ _null_ regexp_split_to_array _null_ _null_ _null_ ));

postgres=# select oid,typname from pg_type where oid = 25 or oid = 1009;

  oid | typname

------+---------

  25 | text

  1009 | _text

(2 rows )

/* the biggie, a compiled RE ( or rather, a front end to same) */

typedef struct

{

  int re_magic; /* magic number */

  size_t re_nsub; /* number of subexpressions */

  long re_info; /* information about RE */

#define REG_UBACKREF 000001

#define REG_ULOOKAHEAD 000002

#define REG_UBOUNDS 000004

#define REG_UBRACES 000010

#define REG_UBSALNUM 000020

#define REG_UPBOTCH 000040

#define REG_UBBS 000100

#define REG_UNONPOSIX 000200

#define REG_UUNSPEC 000400

#define REG_UUNPORT 001000

#define REG_ULOCALE 002000

#define REG_UEMPTYMATCH 004000

#define REG_UIMPOSSIBLE 010000

#define REG_USHORTEST 020000

  int re_csize; /* sizeof( character ) */

  char *re_endp; /* backward compatibility kludge */

  Oid re_collation; /* Collation that defines LC_CTYPE behavior */

  /* the rest is opaque pointers to hidden innards */

  char *re_guts; /* ` char * ' is more portable than `void *' */

  char *re_fns;

} regex_t;

/* result reporting (may acquire more fields later) */

typedef struct

{

  regoff_t rm_so; /* start of substring */

  regoff_t rm_eo; /* end of substring */

} regmatch_t;

typedef long regoff_t;

/*

  * the prototypes for exported functions

  */

extern int pg_regcomp(regex_t *, const pg_wchar *, size_t, int , Oid);

extern int pg_regexec(regex_t *, const pg_wchar *, size_t, size_t, rm_detail_t *, size_t, regmatch_t[], int );

extern int pg_regprefix(regex_t *, pg_wchar **, size_t *);

extern void pg_regfree(regex_t *);

extern size_t pg_regerror( int , const regex_t *, char *, size_t);

extern void pg_set_regex_collation(Oid collation);

int

pg_regcomp(regex_t *re,

   const chr *string, /* 正则表达式字符串 */

   size_t len, /* 正则表达式字符串长度 */

   int flags,

   Oid collation)

int

pg_regexec(regex_t *re, /* 已经用regcomp函数编译好的正则表达式 */

   const chr *string, /* 目标字符串 */

   size_t len, /* 目标字符串长度 */

   size_t search_start, /* 匹配开始位置 */

   rm_detail_t *details, /* NULL */

   size_t nmatch, /* 是regmatch_t结构体数组的长度 */

   regmatch_t pmatch[], /* regmatch_t类型的结构体数组，存放匹配文本串的位置信息 */

   int flags)

/* all the options of interest for regex functions */

typedef struct pg_re_flags

{

  int cflags; /* compile flags for Spencer 's regex code */

  bool glob; /* do it globally (for each occurrence) */

} pg_re_flags;

/*

  * parse_re_flags - parse the options argument of regexp_matches and friends

  *

  * flags --- output argument, filled with desired options

  * opts --- TEXT object, or NULL for defaults

  *

  * This accepts all the options allowed by any of the callers; callers that

  * don' t want some have to reject them after the fact.

  */

static void

parse_re_flags(pg_re_flags *flags, text *opts)

{

  /* regex flavor is always folded into the compile flags */

  flags->cflags = REG_ADVANCED;

  flags->glob = false ;

  if (opts)

  {

  char *opt_p = VARDATA_ANY(opts);

  int opt_len = VARSIZE_ANY_EXHDR(opts);

  int i;

  for (i = 0; i < opt_len; i++)

  {

  switch (opt_p[i])

  {

  case 'g' :

   flags->glob = true ;

   break;

  case 'b' : /* BREs (but why???) */

   flags->cflags &= ~(REG_ADVANCED | REG_EXTENDED | REG_QUOTE);

   break;

  case 'c' : /* case sensitive */

   flags->cflags &= ~REG_ICASE;

   break;

  case 'e' : /* plain EREs */

   flags->cflags |= REG_EXTENDED;

   flags->cflags &= ~(REG_ADVANCED | REG_QUOTE);

   break;

  case 'i' : /* case insensitive */

   flags->cflags |= REG_ICASE;

   break;

  case 'm' : /* Perloid synonym for n */

  case 'n' : /* \n affects ^ $ . [^ */

   flags->cflags |= REG_NEWLINE;

   break;

  case 'p' : /* ~Perl, \n affects . [^ */

   flags->cflags |= REG_NLSTOP;

   flags->cflags &= ~REG_NLANCH;

   break;

  case 'q' : /* literal string */

   flags->cflags |= REG_QUOTE;

   flags->cflags &= ~(REG_ADVANCED | REG_EXTENDED);

   break;

  case 's' : /* single line, \n ordinary */

   flags->cflags &= ~REG_NEWLINE;

   break;

  case 't' : /* tight syntax */

   flags->cflags &= ~REG_EXPANDED;

   break;

  case 'w' : /* weird, \n affects ^ $ only */

   flags->cflags &= ~REG_NLSTOP;

   flags->cflags |= REG_NLANCH;

   break;

  case 'x' : /* expanded syntax */

   flags->cflags |= REG_EXPANDED;

   break;

  default :

   ereport(ERROR,

   (errcode(ERRCODE_INVALID_PARAMETER_VALUE),

   errmsg( "invalid regexp option: \"%c\"" ,

    opt_p[i])));

   break;

  }

  }

  }

}