Caution: These functions do not search for specific html tags. Instead they search for < and > and remove all text in between, including the < and >. Possible problems using these functions are: 1) html tags for carriage return (<br> and <br />) will be replaced without even a space, so there will be no space between 2 sentences in your field; 2) <, >, and & (used to represent <, >, and & in Biotics5) will not be converted; 3) the function will strip out any data containing a < followed by a >, even if < and > were used to represent less than and greater than in a field. (Fields edited in Biotics5 Tracker use html codes in place of < and >, so this issue applies only data edited in Biotics4 or uploaded without converting the characters.)
We recommend that instead you use the HTML2CHAR function (see HTML tags and codes in Biotics5).
These functions will remove the html tags from a string of text. It will replace the tags with anything you want, or nothing at all, if that's what you want. It takes two arguments. One is the string or the column to be stripped. The other is a string that you can specify to replace the stripped out tags. This argument is optional. If you don't supply this argument, the tag will be stripped out and replaced with nothing.
There are two functions in the attached file. One is for VARCHAR2 fields, the other for CLOBs. Depending on what version of Oracle you are running, you may need one or both of these functions. If you are running Oracle 10g or later, you should only need the stripper, which works with VARCHAR2 and CLOBS. If you are running an earlier version of Oracle, you may need the stripperc function for CLOB fields.
CAUSE
Here's an example of how you call the function.
SELECT stripper(formatted_full_citation) FROM reference
This will replace the html tags with nothing.
SELECT stripper(eo_data, '**') FROM EO
This will replace the html tags with '**'. Since a lot of html tags are paragraph breaks, you may want to replace them with something else.
A few things to note:
#1. The Stripper looks for text between < and >. If you have any text between these characters that isn't an html tag, it will still get stripped. You should avoid using these characters for anything other than html, anyway.
#2. If you specify a replacement string, the Stripper will replace all html with that string, not just certain ones.
#3. The Stripper will look for < AND > together. If it finds one without the other, it will ignore the string altogether and do nothing.
#4. If no html tags are found, the string will be returned untouched.
#5. If the html brackets are found out of order, that is the > is found before the <, the string will be returned untouched.
See the script for more examples on how the stripper works.
RESOLUTION
To implement in your database, you must logon to SQL+ as the biotics_user.
Enter:
@ {path where you put the file}\stripper.sql;
Both functions will be added to your database. You could also copy and paste the scripts directly into SQL+ since they are small.