Version: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
Hello,
I need help with a Regular Expression (I think) to replace escaped characters in a string. The data contains special characters that are escaped and stored in their escaped versions.
I need help with replacing any occurrence of these escaped characters with their actual symbol.
Example: If the string contains: ' then I need that replaced with the single quote character.
The characters that are escaped and need replacing are:
' ' (single quote)
’ ' (right side single quote)
& & (ampersand)
“ " (left side double quote)
” " (right side double quote)
" " (double quote)
‘ ' (left side single quote)
– - (dash/hyphen)
<space> (space character)
The data also contains HTML tags that I found a Regular Expression to remove all the HTML characters which is: REGEXP_REPLACE(str, '<[^<>]+>',' ')
What I've done so far is to use the REPLACE function to resolve this, but there has to be a better way. I just don't know it.
This is what I've used so far: replace(replace(replace(replace(replace(replace(REGEXP_REPLACE(str, '<[^<>]+>',' '),'&'||'nbsp;',' '),'<p>',' '),'</p>',' '),'&'||'lsquo;',''''),'&'||'rsquo;',''''),'&'||'amp;','&'))
There has to be a better way!
Please let me know if something isn't clear.
Here is the DDL:
DROP TABLE test PURGE;
CREATE TABLE test
(
str VARCHAR2(2500)
);
INSERT INTO test (str)
VALUES (
'<p>
<strong>Scope</strong>:'||'&'||'nbsp; The goal of this phase of the'||'&'||'nbsp;Program is to securely manage root-similar'||'&'||'nbsp;privileged accounts by implementing Ark'||'&'||'nbsp;and other methods.'||'&'||'nbsp;</p>
<p>
<strong>Current Status</strong>:'||'&'||'nbsp; We use Ark to vault all credentials and have used various methodologies, including Ark, HPNA, and home grown solutions to secure our privilege accounts across our landscape.
Implementation of HPNA and home grown solutions took longer than anticipated, due to the highly complex nature of the work required.
We have decided to re'||'&'||'dash;evaluate and increase the scope of credentials we intend to protect by performing a deep dive inventory to
identify any and all accounts that are '||'&'||'ldquo;root-similar'||'&'||'rdquo;: root, Administrator, or Admin which have full rights.</p>
<p>
<strong>Next Steps</strong>:'||'&'||'nbsp;Continue project work to identify ID'||'&'||'#39;s to be protected, and finalize a plan for implementation.
Once complete, submit project change request for approval in the January change control meeting.'||'&'||'nbsp;</p>');
INSERT INTO test (str)
VALUES (
'<p><strong>Scope: '||'&'||'nbsp;</strong>With the'||'&'||'nbsp;production deployment development teams across the technology
division can now leverage this'||'&'||'nbsp; framework for their internal and external custom'||'&'||'ndash;developed applications. '||'&'||'nbsp;</p>
<p>
<strong>Current Status:</strong>'||'&'||'nbsp; 2014 saw several enhancements as well as 19 applications adopting the framework;
nearly 60% of the applications adopted were labeled as '||'&'||'quot;high risk'||'&'||'quot;.'||'&'||'nbsp; Jan 2015 is expecting'||'&'||'nbsp;8 applications to be released
to Production between the 1/9 and 1/30 dates.'||'&'||'nbsp; 2015 adoption planning is being finalized with the high risk applications first.
A change control will be submitted to change this project'||'&'||'#39;s name to '||'&'||'quot;IAM '||'&'||'ndash; Adoption'||'&'||'quot; '||'&'||'ndash; confirming scope to include all
remaining applications '||'&'||'ndash; and extend the scheduled project completion date to June 30, 2016 at January'||'&'||'#39;s change control meeting.</p>
<p>
<strong>Next Steps:'||'&'||'nbsp;'||'&'||'nbsp;</strong> Finalize 2015 adoption planning, including the risk profile to drive prioritization.'||'&'||'nbsp;
Complete change control to broaden scope beyond 2014 to all applicable applications and extend target date.</p>');
Thanks,
Joe