PostgreSQL 正则表达式替换-使用变量方式
(编辑：jimmy 日期: 2024/11/25 浏览：3 次 )
###不定期更新
把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}(.*"htmlcode">
-- \1表示只匹配第一个子串
select regexp_replace('adsfjjbkk中中','(.)(\1)+','\1','g')
select regexp_replace('adaaasfjjjbkk','(.).*(\1)+','\1','g')
去除字符串最后两位
select substring('abc123d4' from '^(.*"htmlcode">
select * from regexp_matches(' abc123d 4测试 ','[^ ]+','g');
select * from regexp_matches(' abc123d4测试 ','[^ ]+','g');
-output abc123
擦除左右两边的空格
select regexp_replace(' abc123d4 测试 ','^[ ]"htmlcode">
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/" 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+)"htmlcode">
--正向匹配,连继的3个字母右边不能出现:
select * from regexp_matches('asf:::::','[a-zA-Z]{3}("htmlcode">
--仅整数
select * from test where name !~'[0-9]+'
--高大上的写法,包含带符号的整数和浮点数及科学计数
select * from test where name !~'[-+]"htmlcode">
select '(0871)68111111'~'^(\([0-9]{3,4}\)|[0-9]{3,4}-)"htmlcode">
select '+8613000000000'~'^((\+86)|(86)|(\(\+86\))|(\(86\))|(（\+86）)|(（86）))"htmlcode">
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','([-+]"htmlcode">
select * from regexp_matches('Kmblack123456','^("htmlcode">
select * from regexp_matches('1Kmblack123456','^("htmlcode">
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(("htmlcode">
select regexp_replace('所以 font-face 规则实际上是在找到:glyphicons地方''声明"htmlcode">
select 'a123'~'^(("htmlcode">
[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"invalid regexp option: \"%c\"",
opt_p[i])));
break;
}
}
}
}
选项
描述
b
剩余的正则表达式是 BR
c
大小写敏感匹配(覆盖操作符类型)
e
剩余的正则表达式是 ERE
i
大小写不敏感匹配(覆盖操作符类型)
m
n的历史同义词
n
新行敏感匹
p
部分新行敏感匹配
q
重置正则表达式为一个文本("引起")字符串，所有都是普通字符。
s
非新行敏感匹配(缺省)
t
紧语法
w
反转部分新行敏感("怪异")匹配
x
扩展的语法
以上为个人经验，希望能给大家一个参考，也希望大家多多支持。如有错误或未考虑完全的地方，望不吝赐教。
上一篇：Postgresql中LIKE和ILIKE操作符的用法详解
下一篇：PostgreSQL中的COMMENT用法说明