PostgreSQL 正则表达式替换-使用变量方式
###不定期更新
把AAAA替换为A-A-A-A-
javascript
alert('AAAA'.replace(/([A]{1})/g,"$1-"));
()中的内容用变量$1$2$n代替
PostgreSQL
selectregexp_replace('AAAAAAAAAAAAAAAAAAAAAA','([A-Z]{1})','\1-','g')
()中的内容用变量\1\2\n代替
获取大括号中的内容
selectf1[1]fromregexp_matches('asdfadfadsf{_id}','[\{]{1}(.*?)[\}]{1}')asf1
字符串去重
--\1表示只匹配第一个子串
selectregexp_replace('adsfjjbkk中中','(.)(\1)+','\1','g')
selectregexp_replace('adaaasfjjjbkk','(.).*(\1)+','\1','g')
去除字符串最后两位
selectsubstring('abc123d4'from'^(.*?)..$');
-outputabc123
擦除所有空格
select*fromregexp_matches('abc123d4测试','[^]+','g');
select*fromregexp_matches('abc123d4测试','[^]+','g');
-outputabc123
擦除左右两边的空格
selectregexp_replace('abc123d4测试','^[]?(.*?)[]?$','\1','g');
从html中提取字符串
selectf1fromregexp_split_to_table('新闻hao123地图视频贴吧学术登录设置更多产品 取开头4个字符和最后4个字符
withcteas(
select'实际月份少一个月a1.'asflimit1
)select(regexp_matches(f,'^(.{4})'))[1]asstart,(regexp_matches(f,'(.{4})$'))[1]asendfromcte
****提取字段
selectarray_agg(vals[1]),array_agg(vals[2])fromregexp_matches('字段1:值1,字段2:,:3,字段4:4','(\w+)?[:]{1}(\w+)?,?','g')asvals;
selectarray_agg(vals[1]),array_agg(vals[2])fromregexp_matches('字段1=值1,字段2=,=3,字段4=4','(\w+)?[=]{1}(\w+)?,?','g')asvals;
正向匹配和反向匹配
--正向匹配,连继的3个字母右边不能出现:
select*fromregexp_matches('asf:::::','[a-zA-Z]{3}(?!:)')asf1
--反向匹配,连继的3个字母左边不能出现:
select*fromregexp_matches(':::::asdf','(?
查询name字段中不包含数字的记录
--仅整数
select*fromtestwherename!~'[0-9]+'
--高大上的写法,包含带符号的整数和浮点数及科学计数
select*fromtestwherename!~'[-+]?[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})$'
--提取移动电话
selecttmp[8]fromregexp_matches('（+86）13000000000','^((\+86)|(86)|(\(\+86\))|(\(86\))|(（\+86）)|(（86）))?(1[0-9]{10})$','g')astmp
限定用户名
用户名必须由6-16位的A-Z,a-z,0-9,_组成,并且不能为纯数字
constraintck_users_unamecheck(uname~'^[0-9a-zA-Z_]{6,16}$'anduname!~'^[0-9]+$'),
要想给“麻将”和“速度”这两个词加粗
--同时匹配两个字一次,不能用中括号[]
selectregexp_replace('打麻将出老千速度太快将速','((麻将)|(速度){1})','\1','g')
--不正确的写法,请注意看它们之间的区别
selectregexp_replace('打麻将出老千速度太快将速','([麻将|速度]{2})','\1','g')
selectregexp_replace('打麻将出老千速度太快将速','([麻将|速度]{1})','\1','g')
度分秒格式转换为度格式
度精确至小数点6位.只进行了一次浮点运算.
withsplitas(
selectcast(deg[1]asinteger)asdu,
cast(deg[2]asinteger)asfen,
cast(deg[3]asinteger)asmiao,
deg[4]asdirection,
1000000asf1
from(selectregexp_matches('984759','([-+]?[0-9]{1,3})[°]?([0-9]{2})[′]?([0-9]{2})[″]?([SNEW]?)')asdeg)astmp
),cteas(
select
(casewhendu<0or'S'=directionor'W'=directionthen-1else1end)assign,
abs(du)*f1asdu,
fen*f1asfen,
miao*f1asmiao,
cast(f1asfloat8)asf1
fromsplit
)select((du+fen/60+miao/3600)*sign)/f1fromcte;
字符串由数字\大写字母\小写字母\下划线@符号组成，且必须包含数字\大写字母\小写字母，数字\大写字母\小写字母必须至少出现一次，长度为6-14位
select*fromregexp_matches('Kmblack123456','^(?=.*[0-9]+)(?=.*[A-Z]+)(?=.*[a-z]+)[0-9a-zA-Z_@]{6,14}$')asf1
字符串由数字\大写字母\小写字母\下划线@符号组成，并且不能以数字开头，且必须包含数字\大写字母\小写字母，数字\大写字母\小写字母必须至少出现一次，长度为6-14位
select*fromregexp_matches('1Kmblack123456','^(?![0-9]+)(?=.*[0-9]+)(?=.*[A-Z]+)(?=.*[a-z]+)[0-9a-zA-Z_@]{6,14}$')asf1
select*fromregexp_matches('Kmblack123456','^(?![0-9]+)(?=.*[0-9]+)(?=.*[A-Z]+)(?=.*[a-z]+)[0-9a-zA-Z_@]{6,14}$')asf1
日期时间提取
支持1900-2199年的时间,返回的数据索引的含义:
1:完成日期和时间
2.仅日期部分
3.仅年部份
4.年代的头二位(19/20/21)
5.月部分
6.日期部份
7.完整时间部份
8.小时部分
9.分钟部分
10.秒部分
select*fromregexp_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)$')asf1
select*fromregexp_matches('2100-01-0201: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])))$')asf1
把一段字符串中的冒号、单引号、问号前面加上问号这个正则怎么写
selectregexp_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@localhostregex]#pwd
/opt/hgdb-core/src/include/regex
[root@localhostregex]#ll
total40
-rw-r--r--.1postgrespostgres3490Mar1919:00regcustom.h
-rw-r--r--.1postgrespostgres1332Mar1918:59regerrs.h
-rw-r--r--.1postgrespostgres6703Mar1919:00regex.h
-rw-r--r--.1postgrespostgres2353Mar1919:00regexport.h
-rw-r--r--.1postgrespostgres16454Mar1919:00regguts.h
正则表达式编译、匹配、释放、错误信息相关文件，后面再做具体介绍
[root@localhostregex]#pwd
/opt/hgdb-core/src/backend/regex
[root@localhostregex]#llreg*.c
-rw-r--r--.1postgrespostgres55851Mar1919:00regcomp.c
-rw-r--r--.1postgrespostgres3671Mar1918:59regerror.c
-rw-r--r--.1postgrespostgres34873Mar1919:00regexec.c
-rw-r--r--.1postgrespostgres2123Mar1918:59regfree.c
[root@localhostregex]#
内置函数实现在regexp.c
[root@localhostadt]#pwd
/opt/hgdb-core/src/backend/utils/adt
[root@localhostadt]#llregexp.c
-rw-r--r--.1postgrespostgres34863Apr1202:29regexp.c
[root@localhostadt]#
内置函数声明：
/*src/include/catalog/pg_proc.h*/
DATA(insertOID=2073(substringPGNSPPGUID121000fffftfi2025"2525"_null__null__null__null__null_textregexsubstr_null__null__null_));
DESCR("extracttextmatchingregularexpression");
DATA(insertOID=2074(substringPGNSPPGUID141000fffftfi3025"252525"_null__null__null__null__null_"selectpg_catalog.substring($1,pg_catalog.similar_escape($2,$3))"_null__null__null_));
DESCR("extracttextmatchingSQL99regularexpression");
DATA(insertOID=2284(regexp_replacePGNSPPGUID121000fffftfi3025"252525"_null__null__null__null__null_textregexreplace_noopt_null__null__null_));
DESCR("replacetextusingregexp");
DATA(insertOID=2285(regexp_replacePGNSPPGUID121000fffftfi4025"25252525"_null__null__null__null__null_textregexreplace_null__null__null_));
DESCR("replacetextusingregexp");
DATA(insertOID=2763(regexp_matchesPGNSPPGUID121100fffftti201009"2525"_null__null__null__null__null_regexp_matches_no_flags_null__null__null_));
DESCR("findallmatchgroupsforregexp");
DATA(insertOID=2764(regexp_matchesPGNSPPGUID1211000fffftti301009"252525"_null__null__null__null__null_regexp_matches_null__null__null_));
DESCR("findallmatchgroupsforregexp");
DATA(insertOID=2765(regexp_split_to_tablePGNSPPGUID121100000fffftti2025"2525"_null__null__null__null__null_regexp_split_to_table_no_flags_null__null__null_));
DESCR("splitstringbypattern");
DATA(insertOID=2766(regexp_split_to_tablePGNSPPGUID121100000fffftti3025"252525"_null__null__null__null__null_regexp_split_to_table_null__null__null_));
DESCR("splitstringbypattern");
DATA(insertOID=2767(regexp_split_to_arrayPGNSPPGUID121000fffftfi201009"2525"_null__null__null__null__null_regexp_split_to_array_no_flags_null__null__null_));
DESCR("splitstringbypattern");
DATA(insertOID=2768(regexp_split_to_arrayPGNSPPGUID121000fffftfi301009"252525"_null__null__null__null__null_regexp_split_to_array_null__null__null_));
参数类型及返回值类型：
postgres=#selectoid,typnamefrompg_typewhereoid=25oroid=1009;
oid|typname
------+---------
25|text
1009|_text
(2rows)
substring(stringfrompattern)函数提供了从字符串中抽取一个匹配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结构体
/*thebiggie,acompiledRE(orrather,afrontendtosame)*/
typedefstruct
{
intre_magic;/*magicnumber*/
size_tre_nsub;/*numberofsubexpressions*/
longre_info;/*informationaboutRE*/
#defineREG_UBACKREF000001
#defineREG_ULOOKAHEAD000002
#defineREG_UBOUNDS000004
#defineREG_UBRACES000010
#defineREG_UBSALNUM000020
#defineREG_UPBOTCH000040
#defineREG_UBBS000100
#defineREG_UNONPOSIX000200
#defineREG_UUNSPEC000400
#defineREG_UUNPORT001000
#defineREG_ULOCALE002000
#defineREG_UEMPTYMATCH004000
#defineREG_UIMPOSSIBLE010000
#defineREG_USHORTEST020000
intre_csize;/*sizeof(character)*/
char*re_endp;/*backwardcompatibilitykludge*/
Oidre_collation;/*CollationthatdefinesLC_CTYPEbehavior*/
/*therestisopaquepointerstohiddeninnards*/
char*re_guts;/*`char*'ismoreportablethan`void*'*/
char*re_fns;
}regex_t;
存放编译后的正则表达式
regmatch_t结构体
/*resultreporting(mayacquiremorefieldslater)*/
typedefstruct
{
regoff_trm_so;/*startofsubstring*/
regoff_trm_eo;/*endofsubstring*/
}regmatch_t;
typedeflongregoff_t;
成员rm_so存放匹配文本串在目标串中的开始位置，rm_eo存放结束位置。通常我们以数组的形式定义一组这样的结构。
有下面几个主要的函数声明
/*
*theprototypesforexportedfunctions
*/
externintpg_regcomp(regex_t*,constpg_wchar*,size_t,int,Oid);
externintpg_regexec(regex_t*,constpg_wchar*,size_t,size_t,rm_detail_t*,size_t,regmatch_t[],int);
externintpg_regprefix(regex_t*,pg_wchar**,size_t*);
externvoidpg_regfree(regex_t*);
externsize_tpg_regerror(int,constregex_t*,char*,size_t);
externvoidpg_set_regex_collation(Oidcollation);
处理正则表达式常用的函数有pg_regcomp()、pg_regexec()、pg_regfree()和pg_regerror()。
一般处理步骤：编译正则表达式pg_regcomp()，匹配正则表达式pg_regexec()，释放正则表达式pg_regfree()。
pg_regerror()：当执行regcomp或者regexec产生错误的时候，就可以调用这个函数而返回一个包含错误信息的字符串。
参数说明
int
pg_regcomp(regex_t*re,
constchr*string,/*正则表达式字符串*/
size_tlen,/*正则表达式字符串长度*/
intflags,
Oidcollation)
int
pg_regexec(regex_t*re,/*已经用regcomp函数编译好的正则表达式*/
constchr*string,/*目标字符串*/
size_tlen,/*目标字符串长度*/
size_tsearch_start,/*匹配开始位置*/
rm_detail_t*details,/*NULL*/
size_tnmatch,/*是regmatch_t结构体数组的长度*/
regmatch_tpmatch[],/*regmatch_t类型的结构体数组，存放匹配文本串的位置信息*/
intflags)
flags
src/backend/utils/adt/regexp.c
/*alltheoptionsofinterestforregexfunctions*/
typedefstructpg_re_flags
{
intcflags;/*compileflagsforSpencer'sregexcode*/
boolglob;/*doitglobally(foreachoccurrence)*/
}pg_re_flags;
/*
*parse_re_flags-parsetheoptionsargumentofregexp_matchesandfriends
*
*flags---outputargument,filledwithdesiredoptions
*opts---TEXTobject,orNULLfordefaults
*
*Thisacceptsalltheoptionsallowedbyanyofthecallers;callersthat
*don'twantsomehavetorejectthemafterthefact.
*/
staticvoid
parse_re_flags(pg_re_flags*flags,text*opts)
{
/*regexflavorisalwaysfoldedintothecompileflags*/
flags->cflags=REG_ADVANCED;
flags->glob=false;
if(opts)
{
char*opt_p=VARDATA_ANY(opts);
intopt_len=VARSIZE_ANY_EXHDR(opts);
inti;
for(i=0;iglob=true;
break;
case'b':/*BREs(butwhy???)*/
flags->cflags&=~(REG_ADVANCED|REG_EXTENDED|REG_QUOTE);
break;
case'c':/*casesensitive*/
flags->cflags&=~REG_ICASE;
break;
case'e':/*plainEREs*/
flags->cflags|=REG_EXTENDED;
flags->cflags&=~(REG_ADVANCED|REG_QUOTE);
break;
case'i':/*caseinsensitive*/
flags->cflags|=REG_ICASE;
break;
case'm':/*Perloidsynonymforn*/
case'n':/*\naffects^$.[^*/
flags->cflags|=REG_NEWLINE;
break;
case'p':/*~Perl,\naffects.[^*/
flags->cflags|=REG_NLSTOP;
flags->cflags&=~REG_NLANCH;
break;
case'q':/*literalstring*/
flags->cflags|=REG_QUOTE;
flags->cflags&=~(REG_ADVANCED|REG_EXTENDED);
break;
case's':/*singleline,\nordinary*/
flags->cflags&=~REG_NEWLINE;
break;
case't':/*tightsyntax*/
flags->cflags&=~REG_EXPANDED;
break;
case'w':/*weird,\naffects^$only*/
flags->cflags&=~REG_NLSTOP;
flags->cflags|=REG_NLANCH;
break;
case'x':/*expandedsyntax*/
flags->cflags|=REG_EXPANDED;
break;
default:
ereport(ERROR,
(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
errmsg("invalidregexpoption:\"%c\"",
opt_p[i])));
break;
}
}
}
}
选项
描述
b
剩余的正则表达式是BR
c
大小写敏感匹配(覆盖操作符类型)
e
剩余的正则表达式是ERE
i
大小写不敏感匹配(覆盖操作符类型)
m
n的历史同义词
n
新行敏感匹
p
部分新行敏感匹配
q
重置正则表达式为一个文本("引起")字符串，所有都是普通字符。
s
非新行敏感匹配(缺省)
t
紧语法
w
反转部分新行敏感("怪异")匹配
x
扩展的语法
以上为个人经验，希望能给大家一个参考，也希望大家多多支持毛票票。如有错误或未考虑完全的地方，望不吝赐教。