###不定期更新
把AAAA替換為A-A-A-A-
javascript
alert('AAAA'.replace(/([A]{1})/g,"$1-"));
()中的內(nèi)容用變量$1 $2 $n代替
PostgreSQL
select regexp_replace('AAAAAAAAAAAAAAAAAAAAAA','([A-Z]{1})','\1-','g')
()中的內(nèi)容用變量\1 \2 \n代替
獲取大括號(hào)中的內(nèi)容
select f1[1] from regexp_matches('asdfadfadsf{_id}','[\{]{1}(.*?)[\}]{1}') as f1
字符串去重
-- \1表示只匹配第一個(gè)子串
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 rel="external nofollow" name="tj_trnews" class="mnav">新聞/a>a rel="external nofollow" name="tj_trhao123" class="mnav">hao123/a>a rel="external nofollow" name="tj_trmap" class="mnav">地圖/a>a rel="external nofollow" name="tj_trvideo" class="mnav">視頻/a>a rel="external nofollow" name="tj_trtieba" class="mnav">貼吧/a>a rel="external nofollow" name="tj_trxueshu" class="mnav">學(xué)術(shù)/a>a rel="external nofollow" name="tj_login" class="lb" onclick="return false;">登錄/a>a rel="external nofollow" name="tj_settingicon" class="pf">設(shè)置/a>a rel="external nofollow" name="tj_briicon" class="bri" style="display: block;">更多產(chǎn)品/a>/div>','[^>]*>') as f1 where f1>''
取開頭4個(gè)字符和最后4個(gè)字符
with cte as(
select '實(shí)際月份少一個(gè)月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個(gè)字母右邊不能出現(xiàn):
select * from regexp_matches('asf:::::','[a-zA-Z]{3}(?!:)') as f1
--反向匹配,連繼的3個(gè)字母左邊不能出現(xiàn):
select * from regexp_matches(':::::asdf','(?!:)[a-zA-Z]{3}') as f1
查詢name字段中不包含數(shù)字的記錄
--僅整數(shù)
select * from test where name !~'[0-9]+'
--高大上的寫法,包含帶符號(hào)的整數(shù)和浮點(diǎn)數(shù)及科學(xué)計(jì)數(shù)
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位電話號(hào)碼
(871)7位或8位電話號(hào)碼
0871-7位或8位電話號(hào)碼
871-7位或8位電話號(hào)碼
*/
匹配移動(dòng)電話
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})$'
--提取移動(dòng)電話
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,_組成,并且不能為純數(shù)字
constraint ck_users_uname check(uname~'^[0-9a-zA-Z_]{6,16}$' and uname !~ '^[0-9]+$' ),
要想給“麻將”和“速度”這兩個(gè)詞加粗
--同時(shí)匹配兩個(gè)字一次,不能用中括號(hào)[]
select regexp_replace('打麻將出老千速度太快將速','((麻將)|(速度){1})','strong>\1/strong>','g')
--不正確的寫法,請注意看它們之間的區(qū)別
select regexp_replace('打麻將出老千速度太快將速','([麻將|速度]{2})','strong>\1/strong>','g')
select regexp_replace('打麻將出老千速度太快將速','([麻將|速度]{1})','strong>\1/strong>','g')
度分秒格式轉(zhuǎn)換為度格式
度精確至小數(shù)點(diǎn)6位.只進(jìn)行了一次浮點(diǎn)運(yùn)算.
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 du0 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;
字符串由數(shù)字\大寫字母\小寫字母\下劃線@符號(hào)組成,且必須包含數(shù)字\大寫字母\小寫字母,數(shù)字\大寫字母\小寫字母必須至少出現(xiàn)一次,長度為6-14位
select * from regexp_matches('Kmblack123456','^(?=.*[0-9]+)(?=.*[A-Z]+)(?=.*[a-z]+)[0-9a-zA-Z_@]{6,14}$') as f1
字符串由數(shù)字\大寫字母\小寫字母\下劃線@符號(hào)組成,并且不能以數(shù)字開頭,且必須包含數(shù)字\大寫字母\小寫字母,數(shù)字\大寫字母\小寫字母必須至少出現(xiàn)一次,長度為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
日期時(shí)間提取
支持1900-2199年的時(shí)間,返回的數(shù)據(jù)索引的含義:
1:完成日期和時(shí)間
2.僅日期部分
3.僅年部份
4.年代的頭二位(19/20/21)
5.月部分
6.日期部份
7.完整時(shí)間部份
8.小時(shí)部分
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
把一段字符串中的冒號(hào)、單引號(hào)、問號(hào)前面加上問號(hào) 這個(gè)正則怎么寫
select regexp_replace('所以 font-face 規(guī)則實(shí)際上是在找到: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})$'
補(bǔ)充:PostgreSQL 正則表達(dá)式 常用函數(shù)
對那些需要進(jìn)行復(fù)雜數(shù)據(jù)處理的程序來說,正則表達(dá)式無疑是一個(gè)非常有用的工具。本文重點(diǎn)在于闡述 PostgreSQL 的一些常用正則表達(dá)式函數(shù)以及源碼中的一些函數(shù)。
正則相關(guān)部分的目錄結(jié)構(gòu)
[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
正則表達(dá)式編譯、匹配、釋放、錯(cuò)誤信息相關(guān)文件,后面再做具體介紹
[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]#
內(nèi)置函數(shù)實(shí)現(xiàn)在 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]#
內(nèi)置函數(shù)聲明:
/* 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_ ));
參數(shù)類型及返回值類型:
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)
函數(shù)提供了從字符串中抽取一個(gè)匹配 POSIX 正則表達(dá)式模式的子字符串的方法。如果沒有匹配它返回 NULL ,否則就是文本中匹配模式的那部分。
regexp_replace(source, pattern, replacement [, flags ])
函數(shù)提供了將匹配 POSIX 正則表達(dá)式模式的子字符串替換為新文本的功能。
regexp_matches(string, pattern[, flags ])
函數(shù)返回一個(gè)從匹配POSIX正則表達(dá)式模式中獲取的所有子串結(jié)果的text數(shù)組。
參數(shù)flags是一個(gè)可選的text字符串,含有0或者更多單字母標(biāo)記來改變函數(shù)行為。標(biāo)記g導(dǎo)致查找字符串中的每個(gè)匹配,而不僅是第一個(gè),每個(gè)匹配返回一行。
regexp_split_to_table(string, pattern[, flags ])
函數(shù)使用POSIX正則表達(dá)式模式作為分隔符,分隔字符串。返回結(jié)果為string。。
regexp_split_to_array (string, pattern[, flags ])
函數(shù)與regexp_split_to_table行為相同,但,返回結(jié)果為text數(shù)組。
具體使用參考用戶手冊。
src/include/regex/regex.h
regex_t 結(jié)構(gòu)體
/* 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;
存放編譯后的正則表達(dá)式
regmatch_t 結(jié)構(gòu)體
/* 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 存放匹配文本串在目標(biāo)串中的開始位置,rm_eo 存放結(jié)束位置。通常我們以數(shù)組的形式定義一組這樣的結(jié)構(gòu)。
有下面幾個(gè)主要的函數(shù)聲明
/*
* 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);
處理正則表達(dá)式常用的函數(shù)有 pg_regcomp()、pg_regexec()、pg_regfree() 和 pg_regerror()。
一般處理步驟:編譯正則表達(dá)式 pg_regcomp(),匹配正則表達(dá)式 pg_regexec(),釋放正則表達(dá)式 pg_regfree()。
pg_regerror() :當(dāng)執(zhí)行regcomp 或者regexec 產(chǎn)生錯(cuò)誤的時(shí)候,就可以調(diào)用這個(gè)函數(shù)而返回一個(gè)包含錯(cuò)誤信息的字符串。
參數(shù)說明
int
pg_regcomp(regex_t *re,
const chr *string, /* 正則表達(dá)式字符串 */
size_t len, /* 正則表達(dá)式字符串長度 */
int flags,
Oid collation)
int
pg_regexec(regex_t *re, /* 已經(jīng)用regcomp函數(shù)編譯好的正則表達(dá)式 */
const chr *string, /* 目標(biāo)字符串 */
size_t len, /* 目標(biāo)字符串長度 */
size_t search_start, /* 匹配開始位置 */
rm_detail_t *details, /* NULL */
size_t nmatch, /* 是regmatch_t結(jié)構(gòu)體數(shù)組的長度 */
regmatch_t pmatch[], /* regmatch_t類型的結(jié)構(gòu)體數(shù)組,存放匹配文本串的位置信息 */
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;
}
}
}
}
選項(xiàng) |
描述 |
b |
剩余的正則表達(dá)式是 BR |
c |
大小寫敏感匹配(覆蓋操作符類型) |
e |
剩余的正則表達(dá)式是 ERE |
i |
大小寫不敏感匹配(覆蓋操作符類型) |
m |
n的歷史同義詞 |
n |
新行敏感匹 |
p |
部分新行敏感匹配 |
q |
重置正則表達(dá)式為一個(gè)文本("引起")字符串,所有都是普通字符。 |
s |
非新行敏感匹配(缺省) |
t |
緊語法 |
w |
反轉(zhuǎn)部分新行敏感("怪異")匹配 |
x |
擴(kuò)展的語法 |
以上為個(gè)人經(jīng)驗(yàn),希望能給大家一個(gè)參考,也希望大家多多支持腳本之家。如有錯(cuò)誤或未考慮完全的地方,望不吝賜教。
您可能感興趣的文章:- postgresql SQL語句變量的使用說明
- postgresql 導(dǎo)入數(shù)據(jù)庫表并重設(shè)自增屬性的操作
- postgresql coalesce函數(shù)數(shù)據(jù)轉(zhuǎn)換方式
- postgresql 中的COALESCE()函數(shù)使用小技巧
- postgresql 實(shí)現(xiàn)修改jsonb字段中的某一個(gè)值
- postgresql 存儲(chǔ)函數(shù)調(diào)用變量的3種方法小結(jié)