{"id":10,"date":"2010-12-16T13:09:27","date_gmt":"2010-12-16T05:09:27","guid":{"rendered":"http:\/\/j178.mtgbb.com\/278"},"modified":"2018-02-18T22:36:16","modified_gmt":"2018-02-18T14:36:16","slug":"regular-for-oracle-9i","status":"publish","type":"post","link":"https:\/\/j178.mtgbb.com\/?p=10","title":{"rendered":"regular for Oracle 9i"},"content":{"rendered":"<p>\u8cc7\u6599\u4f86\u6e90:\u00a0 <a href=\"http:\/\/phil-sqltips.blogspot.com\/2009\/06\/regexpreplace-regexplike-for-oracle-9i.html\" target=\"_blank\" rel=\"noopener\">phil-sqltips<\/a><\/p>\n<p>\u7528\u9014: Oracle DB 9i \u4e4b\u4e0b\u6c92\u6709 regular function, \u6539\u7528\u5176\u4ed6\u65b9\u5f0f\u9032\u884c<\/p>\n<p>\u6ce8\u610f: \u7562\u7adf\u4e0d\u662f\u539f\u751f function, \u57f7\u884c\u6642\u6548\u80fd\u6548\u80fd\u4e26\u6c92\u6709\u975e\u5e38\u7684\u597d, \u7279\u5225\u50cf\u662f mail list \u9010\u4e00 check \u6642\u6703\u7b49\u5230\u660f\u5012<\/p>\n<p><!--more--><\/p>\n<p>\u7a0b\u5f0f\u78bc\uff1a<\/p>\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"sql\">CREATE OR REPLACE\r\nFUNCTION regexp_replace (\r\nsource_char       IN   VARCHAR2\r\n, pattern           IN   VARCHAR2\r\n, replace_string    IN   VARCHAR2\r\n, POSITION          IN   PLS_INTEGER DEFAULT 1\r\n, occurrence        IN   PLS_INTEGER DEFAULT 0\r\n, match_parameter   IN   VARCHAR2 DEFAULT NULL)\r\nRETURN VARCHAR2 AS\r\n\/*************************************************************************\r\n* Program  : regexp_replace\r\n* Version  : 1.0\r\n* Author   : Philip Moore\r\n* Date     : 20-JUN-2009 Anno Domini\r\n* Purpopse : This provides a pseudo \"REGEXP_REPLACE\" function for Oracle 9iR2\r\n* Warnings : Do NOT compile in an Oracle 10GR2 database (or above)!\r\n*************************************************************************\/\r\n-- Variables\r\nl_source_string                    VARCHAR2 (32767);\r\nl_temp_string                      VARCHAR2 (32767);\r\nl_flags                            VARCHAR2 (10);\r\nl_occurrence                       PLS_INTEGER;\r\nl_end_of_pattern_pos               PLS_INTEGER;\r\nl_string_pos                       PLS_INTEGER;\r\nBEGIN\r\n-- Substr the source_char to start at the position specified\r\nl_source_string := SUBSTR (source_char, POSITION);\r\n \r\n-- Set up the flags argument\r\nIF occurrence = 0 THEN\r\nl_flags := 'g';\r\nELSE\r\nl_flags := NULL;\r\nEND IF;\r\n \r\nl_flags := l_flags || match_parameter;\r\n \r\n-- Now replace the regular expression pattern globally if \"g\"\r\nIF INSTR (l_flags, 'g') &amp;gt; 0 THEN\r\nowa_pattern.CHANGE (line          =&amp;gt; l_source_string\r\n, from_str      =&amp;gt; pattern\r\n, to_str        =&amp;gt; replace_string\r\n, flags         =&amp;gt; l_flags);\r\n-- Not a global replace - loop until the \"occurrence\"th occurrence is replaced...\r\nELSE\r\nl_string_pos := 0;\r\nl_occurrence := 0;\r\nWHILE l_string_pos &amp;lt; LENGTH (l_source_string) AND l_occurrence &amp;lt; occurrence LOOP\r\nl_string_pos := l_string_pos + 1;\r\n \r\nl_end_of_pattern_pos := owa_pattern.amatch (line          =&amp;gt; l_source_string\r\n, from_loc      =&amp;gt; l_string_pos\r\n, pat           =&amp;gt; pattern\r\n, flags         =&amp;gt; match_parameter);\r\n \r\nIF l_end_of_pattern_pos != 0 THEN\r\nl_occurrence := l_occurrence + 1;\r\nEND IF;\r\nEND LOOP;\r\n \r\nIF l_occurrence = occurrence THEN\r\nl_temp_string := SUBSTR (l_source_string\r\n, l_string_pos\r\n, (l_end_of_pattern_pos - l_string_pos));\r\nowa_pattern.CHANGE (line          =&amp;gt; l_temp_string\r\n, from_str      =&amp;gt; pattern\r\n, to_str        =&amp;gt; replace_string\r\n, flags         =&amp;gt; l_flags);\r\nl_source_string := SUBSTR (l_source_string\r\n, 1\r\n, l_string_pos - 1) || l_temp_string || SUBSTR (l_source_string, l_end_of_pattern_pos);\r\nEND IF;\r\nEND IF;\r\n \r\n-- Piece the string back together if needed...\r\nIF POSITION &amp;gt; 1 THEN\r\nl_source_string := SUBSTR (source_char\r\n, 1\r\n, (POSITION - 1)) || l_source_string;\r\nEND IF;\r\n \r\nRETURN l_source_string;\r\nEND regexp_replace;\r\n \r\nCREATE OR REPLACE FUNCTION regexp_like (source_char     IN VARCHAR2\r\n, pattern         IN VARCHAR2\r\n, match_parameter IN VARCHAR2 DEFAULT NULL)\r\nRETURN INTEGER\r\nAS\r\n\/*************************************************************************\r\n* Program  : regexp_like\r\n* Version  : 1.0\r\n* Author   : Philip Moore\r\n* Date     : 20-JUN-2009 Anno Domini\r\n* Purpopse : This provides a pseudo \"REGEXP_LIKE\" operator for Oracle 9iR2\r\n* Warnings : Do NOT compile in an Oracle 10GR2 database (or above)!\r\n*************************************************************************\/\r\n-- Variables\r\nl_return INTEGER;\r\nBEGIN\r\nIF owa_pattern.match (line  =&amp;gt; source_char\r\n, pat   =&amp;gt; pattern\r\n, flags =&amp;gt; match_parameter) THEN\r\nl_return := 1;\r\nELSE\r\nl_return := 0;\r\nEND IF;\r\n \r\nRETURN l_return;\r\nEND regexp_like;<\/pre>\n<p>&nbsp;<\/p>\n<p>\u4f7f\u7528\u7bc4\u4f8b1\uff1a<\/p>\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"sql\">SELECT regexp_replace ('5138675309', '(d{3})(d{3})(d{4})', '(1) 2-3') AS phone\r\nFROM v$version\r\nWHERE banner = 'Oracle9i Enterprise Edition Release 9.2.0.5.0 - 64bit Production';<\/pre>\n<p>\u57f7\u884c\u7d50\u679c:<br \/>\nPHONE<br \/>\n&#8212;&#8212;&#8212;&#8212;&#8212;-<br \/>\n(513) 867-5309<\/p>\n<p>&nbsp;<\/p>\n<p>\u4f7f\u7528\u7bc4\u4f8b2 (Email Address\u9a57\u8b49)\uff1a<\/p>\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"sql\">BEGIN\r\nFOR ii IN 1 .. v_cnt LOOP\r\n--\u627e\u4e0d\u5230','mail \u4f4d\u7f6e\u53ea\u6709\u4e00\u500b\u5c31\u53d6\u5168\u90e8\u7684\u9577\u5ea6\r\nSELECT DECODE(INSTRB(v_email_list, ',', v_fm_pos),\r\n0,\r\nLENGTHB(v_email_list),\r\nINSTRB(v_email_list, ',', v_fm_pos) - 1)\r\nINTO v_to_pos\r\nFROM dual;\r\n \r\nv_len   := v_to_pos - v_fm_pos + 1;\r\nv_email := SUBSTRB(v_email_list, v_fm_pos, ABS(v_len));\r\n \r\nIF regexp_like(v_email,\r\n'^w{1,}[.,0-9,a-z,A-Z,_]w{1,}[.,0-9,a-z,A-Z,_]w{1,}' ||\r\n'@w{1,}[.,0-9,a-z,A-Z,_]w{1,}[.,0-9,a-z,A-Z,_]w{1,}[.,0-9,a-z,A-Z,_]w{1,}$',\r\nNULL) THEN\r\nNULL; -- error\r\nEND IF;\r\n \r\nv_fm_pos := v_to_pos + 2;\r\nEND LOOP;\r\nEND;<\/pre>\n<p>&nbsp;<\/p>\n","protected":false},"excerpt":{"rendered":"<p>\u8cc7\u6599\u4f86\u6e90:\u00a0 phil-sqltips \u7528\u9014: Oracle DB 9i \u4e4b\u4e0b\u6c92\u6709 regular funct &hellip;<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[12],"tags":[23,24],"class_list":["post-10","post","type-post","status-publish","format-standard","hentry","category-database","tag-oracle-db","tag-sql"],"_links":{"self":[{"href":"https:\/\/j178.mtgbb.com\/index.php?rest_route=\/wp\/v2\/posts\/10","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/j178.mtgbb.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/j178.mtgbb.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/j178.mtgbb.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/j178.mtgbb.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=10"}],"version-history":[{"count":0,"href":"https:\/\/j178.mtgbb.com\/index.php?rest_route=\/wp\/v2\/posts\/10\/revisions"}],"wp:attachment":[{"href":"https:\/\/j178.mtgbb.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=10"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/j178.mtgbb.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=10"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/j178.mtgbb.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=10"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}