{"id":19087,"date":"2021-08-04T06:51:03","date_gmt":"2021-08-03T20:51:03","guid":{"rendered":"https:\/\/database.guide\/?p=19087"},"modified":"2021-08-04T06:51:03","modified_gmt":"2021-08-03T20:51:03","slug":"regexp_replace-function-in-oracle","status":"publish","type":"post","link":"https:\/\/database.guide\/regexp_replace-function-in-oracle\/","title":{"rendered":"REGEXP_REPLACE() Function in Oracle"},"content":{"rendered":"\n<p>In Oracle, the <code>REGEXP_REPLACE()<\/code> function replaces occurrences of the substring within a string that matches the given regular expression pattern.<\/p>\n\n\n\n<p>It extends the functionality of the <code><a href=\"https:\/\/database.guide\/replace-function-in-oracle\/\" data-type=\"post\" data-id=\"19081\">REPLACE()<\/a><\/code> function by allowing us to use regular expression patterns. <\/p>\n\n\n\n<!--more-->\n\n\n\n<h2 class=\"wp-block-heading\">Syntax<\/h2>\n\n\n\n<p>The syntax goes like this:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>REGEXP_REPLACE ( source_char, pattern\n                 &#91;, replace_string\n                    &#91;, position\n                       &#91;, occurrence\n                          &#91;, match_param ]\n                       ]\n                    ]\n                 ]\n               )<\/code><\/pre>\n\n\n\n<p>Where:<\/p>\n\n\n\n<ul class=\"wp-block-list\"><li><code><em>source_char<\/em><\/code> is a character expression that serves as the search value.<\/li><li><em><code>pattern<\/code><\/em>&nbsp;is the regular expression.<\/li><li><em><code>replace_string<\/code><\/em>&nbsp;is the replacement string.<\/li><li><em><code>position<\/code><\/em>&nbsp;is a positive integer that specifies where to begin the search. The default is <code>1<\/code>, meaning, start the search at the first character.<\/li><li><em><code>occurrence<\/code><\/em>&nbsp;is a nonnegative integer that specifies which occurrence to replace. The default is <code>0<\/code>, which means replace all occurrences.<\/li><li><em><code>match_param<\/code><\/em>&nbsp;lets you change the default matching behaviour of the function. For example, it allows you to specify case-sensitivity, how multiple lines and spaces are dealt with, etc. This argument works the same as when used with the <code>REGEXP_COUNT()<\/code> function. See <a rel=\"noreferrer noopener\" href=\"https:\/\/docs.oracle.com\/en\/database\/oracle\/oracle-database\/19\/sqlrf\/REGEXP_COUNT.html\" data-type=\"URL\" data-id=\"https:\/\/docs.oracle.com\/en\/database\/oracle\/oracle-database\/19\/sqlrf\/REGEXP_COUNT.html\" target=\"_blank\">Oracle&#8217;s documentation<\/a> for more information.<\/li><\/ul>\n\n\n\n<h2 class=\"wp-block-heading\">Example<\/h2>\n\n\n\n<p>Here&#8217;s a basic example of using <code>REGEXP_REPLACE()<\/code> in Oracle:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT \n    REGEXP_REPLACE('Cats and dogs', 'd.g', 'bird')\nFROM DUAL;<\/code><\/pre>\n\n\n\n<p>Result:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">Cats and birds<\/pre>\n\n\n\n<p>In this case there&#8217;s a match, and the substring is replaced with the replacement string.<\/p>\n\n\n\n<p>Regular expressions can be very powerful, and this example uses a very simple example. In order to use <code>REGEXP_REPLACE()<\/code> effectively, you&#8217;ll need to know the correct pattern to use for the desired outcome. <\/p>\n\n\n\n<h2 class=\"wp-block-heading\">No Match<\/h2>\n\n\n\n<p>Here&#8217;s an example where there&#8217;s no match:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT REGEXP_REPLACE('Cats and dogs', 't.g', 'bird');<\/code><\/pre>\n\n\n\n<p>Result:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">+------------------------------------------------+\n| REGEXP_REPLACE('Cats and dogs', 't.g', 'bird') |\n+------------------------------------------------+\n| Cats and dogs                                  |\n+------------------------------------------------+<\/pre>\n\n\n\n<p>There&#8217;s no match, so the original string is returned unchanged.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Multiple Matches<\/h2>\n\n\n\n<p><meta charset=\"utf-8\">Here&#8217;s an example with multiple matches:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT \n    REGEXP_REPLACE('My dog likes other dogs', 'd.g', 'bird')\nFROM DUAL;<\/code><\/pre>\n\n\n\n<p>Result:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">My bird likes other birds<\/pre>\n\n\n\n<p>However, you can specify which occurrence to replace if required:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT \nREGEXP_REPLACE('My dog likes other dogs', 'd.g', 'bird', 1, 2)\nFROM DUAL;<\/code><\/pre>\n\n\n\n<p>Result:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">My dog likes other birds<\/pre>\n\n\n\n<p>Note that I added two arguments here &#8211;  <code>1<\/code> and <code>2<\/code>. The <code>1<\/code> specifies whereabouts in the string to start the search (in this case, at the first character). The <code>2<\/code> is what specifies which occurrence to replace. In this case, the second occurrence is replaced.<\/p>\n\n\n\n<p>Here&#8217;s what happens if I start the search after the first occurrence:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT \nREGEXP_REPLACE('My dog likes other dogs', 'd.g', 'bird', 7, 2)\nFROM DUAL;<\/code><\/pre>\n\n\n\n<p>Result:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">My dog likes other dogs<\/pre>\n\n\n\n<p>In this case the string isn&#8217;t updated, because there&#8217;s only one more occurrence after the starting position.<\/p>\n\n\n\n<p>If I change the last argument to <code>1<\/code>, then it&#8217;s updated as specified (because it&#8217;s the first occurrence after the specified starting position):<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT \nREGEXP_REPLACE('My dog likes other dogs', 'd.g', 'bird', 7, 1)\nFROM DUAL;<\/code><\/pre>\n\n\n\n<p>Result:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">My dog likes other birds<\/pre>\n\n\n\n<p>And in case you&#8217;re wondering, <code>0<\/code> specifies all occurrences:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT REGEXP_REPLACE(\n    'My dog likes big dogs and small dogs', \n    'd.g', \n    'bird', 1, 0\n    )\nFROM DUAL;<\/code><\/pre>\n\n\n\n<p>Result:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">My bird likes big birds and small birds<\/pre>\n\n\n\n<p>But it still respects any starting position that&#8217;s been specified:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT REGEXP_REPLACE(\n    'My dog likes big dogs and small dogs', \n    'd.g', \n    'bird', 7, 0\n    )\nFROM DUAL;<\/code><\/pre>\n\n\n\n<p>Result:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">My dog likes big birds and small birds<\/pre>\n\n\n\n<h2 class=\"wp-block-heading\">Case Sensitivity<\/h2>\n\n\n\n<p>The <code>REGEXP_REPLACE()<\/code> function follows Oracle&#8217;s collation determination and derivation rules, which define the collation to use when matching the string with the pattern. <\/p>\n\n\n\n<p>However, you can explicitly specify case-sensitivity with the optional sixth argument. When you do this, it overrides any case-sensitivity or accent-sensitivity of the determined collation.<\/p>\n\n\n\n<p>You can specify <code>i<\/code> for case-insensitive matching and <code>c<\/code> for case-sensitive matching.<\/p>\n\n\n\n<p>Here&#8217;s an example:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT \n    REGEXP_REPLACE('My Cats', 'c.t', 'dog', 1, 0) AS \"Default\",\n    REGEXP_REPLACE('My Cats', 'c.t', 'dog', 1, 0, 'i') AS \"Case Insensitive\",\n    REGEXP_REPLACE('My Cats', 'c.t', 'dog', 1, 0, 'c') AS \"Case Sensitive\"\nFROM DUAL;<\/code><\/pre>\n\n\n\n<p>Result:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">   Default    Case Insensitive    Case Sensitive \n__________ ___________________ _________________ \nMy Cats    My dogs             My Cats         <\/pre>\n\n\n\n<p>It appears from these results that my default collation is case-sensitive. The other two strings were forced to a case-insensitive and case-sensitive matching respectively.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Null Arguments<\/h2>\n\n\n\n<p>Passing <code>null<\/code> results in <code>null<\/code> for most arguments, except for the second and sixth arguments:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>SET NULL 'null';\nSELECT \n    REGEXP_REPLACE(null, 'c.t', 'dog', 1, 0, 'i') AS \"1\",\n    REGEXP_REPLACE('Cat', null, 'dog'<meta charset=\"utf-8\">, 1, 0, <meta charset=\"utf-8\">'i') AS \"2\",\n    REGEXP_REPLACE('Cat', 'c.t', null<meta charset=\"utf-8\">, 1, 0, <meta charset=\"utf-8\">'i') AS \"3\",<meta charset=\"utf-8\">\n    REGEXP_REPLACE('Cat', 'c.t', 'dog'<meta charset=\"utf-8\">, null, 0, <meta charset=\"utf-8\">'i') AS \"4\",<meta charset=\"utf-8\">\n    REGEXP_REPLACE('Cat', 'c.t', 'dog'<meta charset=\"utf-8\">, 1, null, <meta charset=\"utf-8\">'i') AS \"5\",<meta charset=\"utf-8\">\n    REGEXP_REPLACE('Cat', 'c.t', 'dog', 1, 0, null) AS \"6\"\nFROM DUAL;<\/code><\/pre>\n\n\n\n<p>Result:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">      1      2       3       4       5      6 \n_______ ______ _______ _______ _______ ______ \nnull    Cat    null    null    null    Cat   <\/pre>\n\n\n\n<p>By default, SQLcl and SQL*Plus return a blank space whenever <code>null<\/code> occurs as a result of a SQL <code><a href=\"https:\/\/database.guide\/sql-select-for-beginners\/\" data-type=\"post\" data-id=\"11928\">SELECT<\/a><\/code> statement. <\/p>\n\n\n\n<p>However, you can use <code><a href=\"https:\/\/database.guide\/set-null-specify-a-string-to-return-whenever-a-null-value-occurs-in-sqlcl-sqlplus\/\">SET NULL<\/a><\/code> to specify a different string to be returned. Here I specified that the string <code>null<\/code> should be returned.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">Wrong Number of Arguments<\/h2>\n\n\n\n<p>Passing no arguments to the function, or too few, results in an error:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT REGEXP_REPLACE()\nFROM DUAL;<\/code><\/pre>\n\n\n\n<p>Result:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">Error starting at line : 1 in command -\nSELECT REGEXP_REPLACE()\nFROM DUAL\nError at Command Line : 1 Column : 8\nError report -\nSQL Error: ORA-00938: not enough arguments for function\n00938. 00000 -  \"not enough arguments for function\"\n*Cause:    \n*Action:<\/pre>\n\n\n\n<p>The same with passing too many arguments:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT REGEXP_REPLACE('Cat', 'c.t', 'dog', 1, 0, 'i', 'oops')\nFROM DUAL;<\/code><\/pre>\n\n\n\n<p>Result:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted\">Error starting at line : 1 in command -\nSELECT REGEXP_REPLACE('Cat', 'c.t', 'dog', 1, 0, 'i', 'oops')\nFROM DUAL\nError at Command Line : 1 Column : 8\nError report -\nSQL Error: ORA-00939: too many arguments for function\n00939. 00000 -  \"too many arguments for function\"\n*Cause:    \n*Action:<\/pre>\n\n\n\n<h2 class=\"wp-block-heading\">More Information<\/h2>\n\n\n\n<p>The <code>REGEXP_REPLACE()<\/code> function (as well as Oracle&#8217;s other implementation of regular expressions) conforms with the IEEE Portable Operating System Interface (POSIX) regular expression standard and to the Unicode Regular Expression Guidelines of the Unicode Consortium.<\/p>\n\n\n\n<p>See the <a href=\"https:\/\/docs.oracle.com\/en\/database\/oracle\/oracle-database\/19\/sqlrf\/REGEXP_REPLACE.html\" data-type=\"URL\" data-id=\"https:\/\/docs.oracle.com\/en\/database\/oracle\/oracle-database\/19\/sqlrf\/REGEXP_REPLACE.html\" target=\"_blank\" rel=\"noreferrer noopener\">Oracle documentation<\/a> for more information and examples of the <code>REGEXP_REPLACE()<\/code> function.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>In Oracle, the REGEXP_REPLACE() function replaces occurrences of the substring within a string that matches the given regular expression pattern. It extends the functionality of the REPLACE() function by allowing us to use regular expression patterns.<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[41],"tags":[93,86,67,20],"class_list":["post-19087","post","type-post","status-publish","format-standard","hentry","category-oracle","tag-functions","tag-regex","tag-string-functions","tag-what-is"],"_links":{"self":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/19087","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/comments?post=19087"}],"version-history":[{"count":21,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/19087\/revisions"}],"predecessor-version":[{"id":19248,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/posts\/19087\/revisions\/19248"}],"wp:attachment":[{"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/media?parent=19087"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/categories?post=19087"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/database.guide\/wp-json\/wp\/v2\/tags?post=19087"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}