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

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

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

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

 select f1 from regexp_split_to_table('
新闻hao123地图视频贴吧学术登录设置更多产品
','<[^>]*>') as f1 where f1<>''

 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','(?

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

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

 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;

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

 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

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

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

 [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]# 

 [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 matc
                
提示：
                    本文由神整理自网络，如有侵权请联系本站删除！
                    

                    本站声明： 

                    1、本站所有资源均来源于互联网，不保证100%完整、不提供任何技术支持； 

                    2、本站所发布的文章以及附件仅限用于学习和研究目的;不得将用于商业或者非法用途；否则由此产生的法律后果，本站概不负责！
                
                

                

                                            
上一篇：PostgreSQL11修改wal-segsize的操作_PostgreSQL_
                                                                
下一篇：关于PostgreSQL错误日志与慢查询日志收集_PostgreSQL_