欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

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

程序员文章站 2022-03-03 11:41:00
###不定期更新把aaaa替换为a-a-a-a-javascriptalert('aaaa'.replace(/([a]{1})/g,"$1-"));()中的内容用变量$1 $2 $n代替postgr...

###不定期更新

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

字符串由数字\大写字母\小写字母\下划线@符号组成,且必须包含数字\大写字母\小写字母,数字\大写字母\小写字母必须至少出现一次,长度为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 f1
select * 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 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')

必须以字母开头,且长度为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]# 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]# 

内置函数实现在 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 产生错误的时候,就可以调用这个函数而返回一个包含错误信息的字符串。

参数说明

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)

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 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;
 }
 }
 }
}
选项 描述
b 剩余的正则表达式是 br
c 大小写敏感匹配(覆盖操作符类型)
e 剩余的正则表达式是 ere
i 大小写不敏感匹配(覆盖操作符类型)
m n的历史同义词
n 新行敏感匹
p 部分新行敏感匹配
q 重置正则表达式为一个文本("引起")字符串,所有都是普通字符。
s 非新行敏感匹配(缺省)
t 紧语法
w 反转部分新行敏感("怪异")匹配
x 扩展的语法

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