这篇文章主要介绍了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中提取字符串
取开头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','(?查询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})','\1','g') --不正确的写法,请注意看它们之间的区别 select regexp_replace('打麻将出老千速度太快将速','([麻将|速度]{2})','\1','g') select regexp_replace('打麻将出老千速度太快将速','([麻将|速度]{1})','\1','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 'w'=direction then -1 else 1 end) as sign, abs(du) * f1 du, fen fen, miao miao, cast(f1 float8) from split )select ((du + 60 3600) sign)>字符串由数字\大写字母\小写字母\下划线@符号组成，且必须包含数字\大写字母\小写字母，数字\大写字母\小写字母必须至少出现一次，长度为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 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 | 扩展的语法 |
以上就是PostgreSQL 正则表达式替换-使用变量方式的详细内容，更多请关注0133技术站其它相关文章！
