StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>Oracle practitioners survived for years without regular expressions because Oracle provides some simple string functions which we can combine for some nifty manipulation. </p> <p>For instance, to find the first two characters after the last underscore in a string use SUBSTR() and INSTR() like this: </p> <pre><code>with t as (select 'SPRINTMVNO_PM_CDR_IWIRELESS_20121110_0813.csv' str from dual) select substr(str, instr(str, '_', -1)+1, 2) from t / </code></pre> <p>Note the INSTR() call has a negative offset to start counting from the back. Getting the last four characters of a string employs the same trick:</p> <pre><code>with t as (select 'iwireless_201211120015_201211120515' str from dual) select substr(str, -4) from t / </code></pre> <p>The easiest way to identify a pattern of <em>underscore followed by digits followed by underscore</em> is with a regex but we can use a TRIM() to remove the underscores from the result.</p> <pre><code>with t as (select 'RK_IPDR_RKMSG2_0043722_DT_20121113162710.txt' str from dual) select trim('_' from regexp_substr(str, '_([0-9]+)_')) from t / </code></pre> <p>Here's <a href="http://sqlfiddle.com/#!4/1c9f3/10" rel="nofollow">a SQL Fiddle</a> to prove that these techniques work. </p> <p>Oracle has a vast array of functions, which are described in the documentation. <a href="http://docs.oracle.com/cd/E11882_01/server.112/e26088/functions002.htm#CJAJHBIA" rel="nofollow">Find out more</a>.</p> <hr> <blockquote> <p>" please ignore the cases, I just need a solution of this 'how to get content between or start with a character but not include it, with Oracle's regex ?'"</p> </blockquote> <p>There is a way to exclude characters from the start or end of the result, and that is to break up the search pattern into sub-expressions. This will work for the string you provide, because we can separate the leading and trailing underscores from the required numbers. Unfortunately, the <em>subexpressions</em> parameter is <a href="http://docs.oracle.com/cd/E11882_01/server.112/e26088/functions150.htm#i1239858" rel="nofollow">the last parameter in the REGEXP_SUBSTR() signature</a>, and as SQL functions don't accept named parameters this means we have to explicitly pass default values for all the other parameters.</p> <p>Anyway, this call will return the second subexpression, which is the desired string, <code>0043722</code>:</p> <pre><code>with t as (select 'RK_IPDR_RKMSG2_0043722_DT_20121113162710.txt' str from dual) select regexp_substr(str, '(_)([0-9]+)(_)', 1,1,'i',2) from t / </code></pre> <hr> <p>The use cases <em>do</em> matter. The REGEXP functions perform slower than the simpler equivalents. In 10gR2 REGEXP_SUBSTR() is at least an order of magnitude slower than SUBSTR(). The difference is noticeable when searching large numbers of strings, and crippling when that number becomes millions (disclosure: recent pain).</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload