Oracle 12.1 example of regexp_substr usage - filter single quote

Goal: Let us see how can we filter out only the single quoted string from the below text.

Query 1:
select ('Hi this is ''ramu''') from dual;


SQL> select ('Hi this is ''ramu''') from dual;

('HITHISIS''RAMU'
-----------------
Hi this is 'ramu'

Solution:
select regexp_substr('Hi this is ''ramu''','''([[:alnum:]]+'')') from dual;

Explanation:
Examine the actual substr expression the below way, you will know what exactly is your substring filter.

SQL> select ('''([[:alnum:]]+'')') from dual;

('''([[:ALNUM:]]
----------------
'([[:alnum:]]+')

Here,
(i) ''' (single quotes) in the regular expression filter is '''([[:alnum:]]+'')' actually translated to a 2 single quote 1 is used as the format for substr expression

and other is used for the search (here this indicates start my search from first single quote apperarence).
(ii) bracets () covering is used again as a format controller to group the next set of search conditions
(iii) []+ tells that I should look for text starting with single quote and what ever is mentioned within this enclosure, which here is a substring of alphanumeric

characters which is [:alnum:].
(iv) '')' tells that I should end my search with the next consective appearence of a single quote (indicated by ''). We close our search with the closing bracket.

Hope this is clear.

Let us evaulate weather the explanation holds correct by another example...

SQL> select ('Hi this is ''ramu'' and here is the ''rose''') string_tosearch from dual;

STRING_TOSEARCH
----------------------------------------
Hi this is 'ramu' and here is the 'rose'

SQL> select regexp_substr('Hi this is ''ramu'' and here is the ''rose''','''([[:alnum:]]+'')') srch_result from dual;

SRCH_R
------
'ramu'

let us see how can we filter out the second consecutive occurence of the single quoted string.


Solution:
select regexp_substr('Hi this is ''ramu'' and here is the ''rose''','''([[:alnum:]]+'')',1,2) srch_result from dual;

SQL> select ('Hi this is ''ramu'' and here is the ''rose''') string_tosearch from dual;

STRING_TOSEARCH
----------------------------------------
Hi this is 'ramu' and here is the 'rose'

SQL> select regexp_substr('Hi this is ''ramu'' and here is the ''rose''','''([[:alnum:]]+'')',1,2) srch_result from dual;

SRCH_R
------
'rose'

Explanation:
a)  Here ,1,2 within the brackets indicate that the search should begin from 1st position and look for second consecutive occurrence of the matching pattern.

if I wanted to search for what ever starting with ^ until ! or end of line...

select ('Hi,this is''nt a joke!, we know where to look for! if we dont find him in ^Krish will come! back to you!') string_tosearch from dual;

SQL> select ('Hi,this is''nt a joke!, we know where to look for! if we dont find him in ^Krish will come! back to you!') string_tosearch from dual;

STRING_TOSEARCH
-------------------------------------------------------------------------------------------------------
Hi,this is'nt a joke!, we know where to look for! if we dont find him in ^Krish will come! back to you!

Solution:
select regexp_substr('Hi,this is''nt a joke!, we know where to look for! if we dont find him in ^Krish will come! back to you!','\^[^!]+!') srch_result from dual;

SQL> select regexp_substr('Hi,this is''nt a joke!, we know where to look for! if we dont find him in ^Krish will come! back to you!','\^[^!]+!') srch_result from dual;

SRCH_RESULT
-----------------
^Krish will come!

Here what we did was we looked @ first occurence of a pattern which started with ^ and ended with !, we get both leading and trailing characters printed here.

The '+' symbol signifies we search for one or more occurrence of the  preceding search pattern.







Thanks

Conclusion: We are correctly filtering out the sub-string enclosed by a single quote in the position we call out. We even filtered out special characters.

Use case: Being a DBA you can use this to filter out literals used in sqltext