APRIL 3, 2014; REVISED Nov 19, 2014
In early releases of Biotics 5, the rich-text editor in Biotics 5 inserted some unwanted HTML codes for "special" characters when you edited a field:
|
Symbol |
Code |
| " (double quotation mark) | " |
| ' (single quotation mark or apostrophe) |
' |
| nonbreaking space |
|
This article tells you how to get rid of these codes (and only these codes) in the actual database fields where they exist. Once removed, they will not come back and you won't see them in exported data.
If you click the Source button in the header of a rich-text field, you can see in your text, but the codes of single and double quotation marks do not appear. Nevertheless, they are there and will appear in exported data and query results.
The addition of was fixed in release 5.3, and the " and ' codes were no longer added as of release 5.4 (mid-May 2014). However, any rich-text field that you edited prior to those releases may have been infiltrated with these codes. To find them, you can search for these codes as you would any other text string. For example, if you edited the EO Data field in a bunch of records and want to know if any of the codes lurk there, use a query like the following. Substitute '%'%' to search for that code, etc.
select eo_id, eo_data
from eo
where eo_data like '% %'
Once you find out which fields and records contain these codes, you can get rid of them with SQL update statements.
NOTE #1: Remember, you can't run updates inside Biotics 5 Query Builder; you'll have to log into your database through a tool such as SQL+, SQL Developer, or Toad.
NOTE #2: You cannot replace the codes for <,>, or &. Those characters must remain encoded as html inside Biotics text fields. (See HTML tags and codes in Biotics5 for help with <,>, and &.)
NOTE #3: You must include the first line "set define off" or this update won't work.
Example 1--replacing codes in a clob field:
set define off
update eo set eo_data = to_clob(replace(eo_data, ' ', ' ')), rec_last_mod_user = 'me'
where eo_id in (1,2,3,4,5,etc.);
commit;
Example 2--replacing codes in another clob field:
set define off
update eo set directions = to_clob(replace(directions, ''', '''')), rec_last_mod_user = 'me'
where eo_id in (1,2,3,4,5,etc.);
commit;
Example 3--replacing codes in a non-clob field (note the difference in syntax):
set define off
update eo set mgmt_com = replace(mgmt_com, '"', '"'), rec_last_mod_user = 'me'
where eo_id in (1,2,3,4,5,etc.);
commit;
Example 1 update looks for the code in the specified field and replaces it with a single space (since the code stood for a single space, you're not adding another space, you just preserving it).
Example 2 looks for ' and replaces it with an apostrophe or single quote. The symbols '''' are 4 single quotes in a row (two single-quotes enclosed by two other single quote marks).
Example 3 looks for " and replaces it with a double-quote mark. The symbols '"' are a double-quote enclosed by two other single quote marks.
You don't have to include a list of record IDs, but if you don't the update will run on every record, regardless of whether it contains the code, and will change the rec_last_mod_date and rec_last_mod_user for every record, so beware.
IMPORTANT: The name of the field that you are cleaning up always must appear TWICE in the update statement:
set eo_data = to_clob(replace(eo_data, ' ', ' '))
If you are doing updates to clean several fields, don't forget to change the field name in both places!!
If you have questions or concerns about cleaning up these codes, please submit a ticket.
ALSO SEE Solutions:
Crystal Reports: appears in long text fields in Crystal Reports
HTML tags and codes in Biotics5