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

###不定期更新

把AAAA替换为A-A-A-A-

javascript

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

()中的内容用变量$1 $2 $n代替

PostgreSQL

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

()中的内容用变量\1 \2 \n代替

获取大括号中的内容

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');
    

从htML中提取字符串

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>
    ''

取开头4个字符和最后4个字符

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

查询name字段中不包含数字的记录

--仅整数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

限定用户名

用户名必须由6-16位的A-Z,a-z,0-9,_组成,并且不能为纯数字

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')

度分秒格式转换为度格式

度精确至小数点6位.只进行了一次浮点运算.

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 du0 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;
    

字符串由数字\大写字母\小写字母\下划线@符号组成，且必须包含数字\大写字母\小写字母，数字\大写字母\小写字母必须至少出现一次，长度为6-14位

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

字符串由数字\大写字母\小写字母\下划线@符号组成，并且不能以数字开头，且必须包含数字\大写字母\小写字母，数字\大写字母\小写字母必须至少出现一次，长度为6-14位

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

日期时间提取

支持1900-2199年的时间,返回的数据索引的含义:

1:完成日期和时间

2.仅日期部分

3.仅年部份

4.年代的头二位(19/20/21)

5.月部分

6.日期部份

7.完整时间部份

8.小时部分

9.分钟部分

10.秒部分

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 f1select * 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')

必须以字母开头,且长度为4-16的字符串

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}
    )$'

补充：PostgreSQL 正则表达式 常用函数

对那些需要进行复杂数据处理的程序来说，正则表达式无疑是一个非常有用的工具。本文重点在于阐述 PostgreSQL 的一些常用正则表达式函数以及源码中的一些函数。

正则相关部分的目录结构

[root@localhost regex]# pwd/opt/hGDB-core/src/include/regex[root@localhost regex]# lltotal 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]# 

内置函数实现在 regexp.c

[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)

substring(string from pattern)函数提供了从字符串中抽取一个匹配 posix 正则表达式模式的子字符串的方法。如果没有匹配它返回 NULL ，否则就是文本中匹配模式的那部分。

regexp_replace(source, pattern, replacement [, flags ])函数提供了将匹配 POSIX 正则表达式模式的子字符串替换为新文本的功能。

regexp_matches(string, pattern[, flags ])函数返回一个从匹配POSIX正则表达式模式中获取的所有子串结果的text数组。

参数flags是一个可选的text字符串，含有0或者更多单字母标记来改变函数行为。标记g导致查找字符串中的每个匹配，而不仅是第一个，每个匹配返回一行。

regexp_split_to_table(string, pattern[, flags ])函数使用POSIX正则表达式模式作为分隔符，分隔字符串。返回结果为string。。

regexp_split_to_array (string, pattern[, flags ])函数与regexp_split_to_table行为相同，但，返回结果为text数组。

具体使用参考用户手册。

src/include/regex/regex.h

regex_t 结构体

/* 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;
    

存放编译后的正则表达式

regmatch_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;
    

成员rm_so 存放匹配文本串在目标串中的开始位置，rm_eo 存放结束位置。通常我们以数组的形式定义一组这样的结构。

有下面几个主要的函数声明

/* * 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);
    

处理正则表达式常用的函数有 pg_regcomp()、pg_regexec()、pg_regfree() 和 pg_regerror()。

一般处理步骤：编译正则表达式 pg_regcomp()，匹配正则表达式 pg_regexec()，释放正则表达式 pg_regfree()。

pg_regerror() ：当执行regcomp 或者regexec 产生错误的时候，就可以调用这个函数而返回一个包含错误信息的字符串。

参数说明

intpg_regcomp(regex_t *re,  const chr *string, /* 正则表达式字符串 */  size_t len, /* 正则表达式字符串长度 */  int flags,  Oid collation) intpg_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)

flags

src/backend/utils/adt/regexp.c

/* 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 voidparse_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;
 }
 }
 }
}
    

以上为个人经验，希望能给大家一个参考，也希望大家多多支持。如有错误或未考虑完全的地方，望不吝赐教。

• postgresql SQL语句变量的使用说明
• postgresql 导入数据库表并重设自增属性的操作
• postgresql coalesce函数数据转换方式
• postgresql 中的COALESCE()函数使用小技巧
• postgresql 实现修改jsonb字段中的某一个值
• postgresql 存储函数调用变量的3种方法小结

声明：本文内容由网友自发贡献，本站不承担相应法律责任。对本内容有异议或投诉，请联系2913721942#qq.com核实处理，我们将尽快回复您，谢谢合作！