Regular Expressions to the Rescue

A few situations where using regular expressions led to better, cleaner and leaner code. More information is available, of course, in the manual.

--remove all special characters except the following:
--those between ' ' and '{'
--'}' and '~'
--the number of nested REPLACEs in the code that I had to clean up
--gave me a serious headache
SELECT REGEXP_REPLACE(:p_string,'[^[ -{,},~]]*','') FROM dual;

Short Explanation:
–  : Range operator, used here to indicate the range ‘ ‘ to ‘{‘
[ … ]  : Matches any single character in the list within the brackets
[^ … ] : Matches any single character not in the list within the brackets
*  : Matches zero or more occurrences of the preceding subexpression
So, the expression [^[<list of characters>]]* matches all characters which are not in <list of characters>

--in a given string
--if a double-quote is preceded by letter(s), the output should be a string with the double-quote (") replaced with two double-quotes ("")
--if a double-quote is preceded by number(s) then the output should be a string where the number(s) is enclosed within double-quotes
--for example
--abc"d should produce abc""d as output
--ab"c"d should produce ab""c""d as output
--abc"d12" should produce abc""d"12" as output
--ab12"c34" should produce ab"12"c"34" as output
WITH mydata AS(
select 'abc"d' test_string from dual union all
select 'ab"c"d' test_string from dual union all
select 'abc"d12"' test_string from dual union all
select 'abc"d5"' test_string from dual union all
select 'ab12"c34"' test_string from dual
)
SELECT test_string
,regexp_replace(test_string,'([[:digit:]]*")','"\1') result_string
FROM mydata;

Short Explanation:
[ … ] : Matches any single character in the list within the brackets
[[:digit:]] : POSIX character class which matches digits
* : Matches zero or more occurrences of the preceding subexpression
( … ) : Treats the expression within parentheses as a unit or subexpression
\n : Back reference. Matches the nth preceding subexpression. The \1 used here refers to the first preceding subexpression which is ([[:digit:]]*”)
So, the expression ([[:digit:]]*”) matches all double-quotes optionally preceded by numbers and replaces it with the a double-quote followed by the matched expression.

--Given a string of the form HOURS:MINUTES:SECONDS (ex. 11:32:09)
--add the values of HOURS, MINUTES and SECONDS to SYSDATE
SELECT TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS') sys_date
, TO_CHAR(SYSDATE + NUMTODSINTERVAL(REGEXP_SUBSTR (:p_string,'[^:]+',1, 1),'HOUR') --add hours
+ NUMTODSINTERVAL(REGEXP_SUBSTR (:p_string,'[^:]+',1, 2), 'MINUTE') --add minutes
+ NUMTODSINTERVAL(REGEXP_SUBSTR (:p_string,'[^:]+',1, 3),'SECOND') --add second
,'DD-MON-YYYY HH24:MI:SS') sys_date_added_to_tag from dual;

Short Explanation:
[^ … ] : Matches any single character not in the list within the brackets
+ : Matches one or more occurrences of the preceding subexpression.
So, the expression [^:]+ will return one or more consecutive characters which are not colons and hence will return 11, 32 and 09