oracle中使用REGEXP oracle regexp_substr中正则表达式问题
oracle\u4e2d\u4f7f\u7528regexp_substr\u53d6\u503c\u3002\uff08\u6700\u597d\u7528\u6b63\u5219\uff09select regexp_substr(regexp_substr('','a2=".*"'),'\w+',1,2) from dual
\u7ed3\u679c\uff1ab22222\uff0c\u5982\u65e0a2\u65f6\uff0c\u503cnull
[[:alpha:]] \u4efb\u4f55\u5b57\u6bcd\u3002
[[:digit:]] \u4efb\u4f55\u6570\u5b57\u3002
[[:alnum:]] \u4efb\u4f55\u5b57\u6bcd\u548c\u6570\u5b57\u3002
[[:space:]] \u4efb\u4f55\u767d\u5b57\u7b26\u3002
[[:upper:]] \u4efb\u4f55\u5927\u5199\u5b57\u6bcd\u3002
[[:lower:]] \u4efb\u4f55\u5c0f\u5199\u5b57\u6bcd\u3002
[[:punct:]] \u4efb\u4f55\u6807\u70b9\u7b26\u53f7\u3002
'[^,|+|*|/|-]+', 1, level) as str
from dual
connect by level <= regexp_count('020101:1:13,020101:1:175,020301:1:107+020401:1:140-020501:1:107,020301:1:107*020401:1:140/020501:1:107',
'[,|+|*|/|-]') + 1
order by str;
绛旓細oracle浣跨敤姝ｅ垯琛ㄨ揪寮忓垪鍏ヤ袱涓浐瀹氬瓧绗︿腑闂寸殑鏁版嵁鏂规硶锛1銆佹鍒欒〃杈惧紡鐨勪娇鐢闇瑕佺敤鍒板叧閿瓧REGEXP锛岀敤瀹氫箟涓缁勫瓧绗︼紝琛ㄧず鍖归厤125涓変釜瀛楃銆2銆乨igit琛ㄧず鍖归厤浠绘剰鏁板瓧锛孌OT琛ㄧずALL琛ㄧず鎵鏈夛紝杩炶捣鏉ュ尮閰嶅寘鎷崲琛岀榛樿妯″紡涓.鏄笉鑳藉尮閰嶈绗鐨勩
绛旓細m锛氬琛屾ā寮忥紱x锛氭墿灞曟ā寮忥紝蹇界暐姝ｅ垯琛ㄨ揪寮忎腑鐨绌虹櫧瀛楃銆傚叏閮ㄦ祴璇曟暟鎹甋QL> SELECT * FROM test_reg_like;A---ABCA1212a12鍖归厤瀛楁瘝A鐨凷QL> SELECT 2 * 3 FROM 4 test_reg_like 5 WHERE 6 REGEXP_LIKE(a, 'A');A---ABCA12鍖归厤瀛楁瘝A鐨 锛堝ぇ灏忓啓涓嶆晱鎰燂級SQL> SELECT ...
绛旓細鍦Oracle涓锛屽垽鏂煡璇㈢粨鏋滀腑鐨勫垪鏄惁鍖呭惈瀛楁瘝锛屽彲浠浣跨敤姝ｅ垯琛ㄨ揪寮忓嚱鏁regexp_like銆傚叿浣撴搷浣滃涓嬶細鎵ц浠ヤ笅SQL璇彞锛歋ELECTCASEWHENREGEXP_LIKE(瀛楁鍚,'.([a-z]+|[A-Z])')THEN'鍖呭惈瀛楁瘝'ELSE'涓嶅寘鍚瓧姣'END FROM琛ㄥ悕瀛;杩欓噷鐨勬鍒欒〃杈惧紡鍏抽敭鍦ㄤ簬绗簩涓弬鏁帮細'.([a-z]+|[A-Z])'锛屽叾涓'.'...
绛旓細Select regexp_substr('asdfafdda>fda','<[^<]+>') from dualOutput: da>鍦ㄨ繖涓緥瀛愪腑,鎴戜滑鍦ㄤ箣鍚庤繕鍦╠a>,杩欐牱鐨璇,濡傛灉鎴戜滑娌℃湁娣诲姞^>,姝ｅ垯琛ㄨ揪寮忓氨浼氬悜鍚庣户缁幓鍖归厤,鐩村埌鏈鍚庝竴涓>涓鸿嚦,杩欐牱灏变細鍑虹幇鍋忓樊 杩欎釜閫氬父鐢ㄦ潵瀹炵幇瀛楃涓茬殑鍒椾紶琛宻elect regexp_substr('123;234;345;456;567;678;789','[^;]...
绛旓細鐢 regexp_like杩欎釜鍑芥暟鏉ヨВ鍐筹紝姝ｅ垯琛ㄨ揪寮忎负锛歔[:punct:]]+杩欎釜姝ｅ垯鍙互鎵惧嚭浠讳綍鏍囩偣绗﹀彿銆傛煡璇alue涓寘鍚换浣曟爣鐐圭鍙鐨璁板綍濡備笅锛歴elect * from xxx where regexp_like(value,'[[:punct:]]+');POSIX 姝ｅ垯琛ㄨ揪寮忕敱鏍囧噯鐨勫厓瀛楃锛坢etacharacters锛夋墍鏋勬垚锛 '$' 鍖归厤杈撳叆瀛楃涓茬殑缁撳熬浣嶇疆銆傚鏋滆缃...
绛旓細Oracle 10g鏀寔姝ｅ垯琛ㄨ揪寮忕殑鍥涗釜鏂板嚱鏁板垎鍒槸锛REGEXP_LIKE銆丷EGEXP_INSTR銆丷EGEXP_SUBSTR銆佸拰REGEXP_REPLACE銆傚畠浠娇鐢≒OSIX 姝ｅ垯琛ㄨ揪寮忎唬鏇夸簡鑰佺殑鐧惧垎鍙凤紙%锛夊拰閫氶厤绗︼紙_锛夊瓧绗︺傜壒娈婂瓧绗︼細'^' 鍖归厤杈撳叆瀛楃涓茬殑寮濮嬩綅缃紝鍦ㄦ柟鎷彿琛ㄨ揪寮涓娇鐢锛屾鏃跺畠琛ㄧず涓嶆帴鍙楄瀛楃闆嗗悎銆'$' 鍖归厤杈撳叆瀛楃涓茬殑缁撳熬...
绛旓細涓嬮潰涓昏浠嬬粛涓Oracle涓琛ㄨ揪寮忓嚱鏁REGEXP_LIKE鐨勪娇鐢锛歊EGEXP_LIKE(x, pattern [, match_option])褰撴簮瀛楃涓瞲鍖归厤姝ｅ垯琛ㄨ揪寮弍attern鏃讹紝杩斿洖true銆傚彲浠ヤ娇鐢╩atch_option淇敼榛樿鍖归厤閫夐」锛岃鍙傛暟鍙互琚缃负锛- 'c'锛 璇存槑鍦ㄨ繘琛屽尮閰嶆椂鍖哄垎澶у皬鍐欙紙榛樿閫夐」锛- 'i'锛 璇存槑鍦ㄨ繘琛屽尮閰嶆椂涓嶅尯鍒嗗ぇ灏忓啓 ...
绛旓細鍙互閫氳繃REGEXP_SUBSTR鍑芥暟锛堥厤鍚堟鍒欒〃杈惧紡锛夋潵瀹炵幇瀛楃涓叉埅鍙栥備妇渚嬶細sql:select regexp_substr('CYJ8-ABC','[0-9]+') from dual;浠ヤ笂sql鎵ц缁撴灉锛8锛涘娉細REGEXP_SUBSTR鍑芥暟鏍煎紡濡備笅锛歠unction REGEXP_SUBSTR(String, pattern, position, occurrence, modifier)__srcstr 锛氶渶瑕佽繘琛屾鍒欏鐞鐨瀛楃...
绛旓細濡傛灉Oracle鐗堟湰涓嶆槸澶綆鐨勮瘽锛浣跨敤 姝ｅ垯琛ㄨ揪寮忓嚱鏁 REGEXP_SUBSTR 澶勭悊銆俓x0d\x0a\x0d\x0a5涓弬鏁癨x0d\x0a绗竴涓槸杈撳叆鐨勫瓧绗︿覆\x0d\x0a绗簩涓槸姝ｅ垯琛ㄨ揪寮廫x0d\x0a绗笁涓槸鏍囪瘑浠庣鍑犱釜瀛楃寮濮嬫鍒欒〃杈惧紡鍖归厤銆傦紙榛樿涓1锛塡x0d\x0a绗洓涓槸鏍囪瘑绗嚑涓尮閰嶇粍銆傦紙榛樿涓1锛塡x...
绛旓細select distinct regexp_substr('020101:1:13,020101:1:175,020301:1:107+020401:1:140-020501:1:107,020301:1:107*020401:1:140/020501:1:107','[^,|+|*|/|-]+', 1, level) as str from dual connect by level <= regexp_count('020101:1:13,020101:1:175,020301:1:107+020401...
