losing capitalisation

[gav12345]

Hi,

We have a clob field which contains text with html formatting tags. Since the tags are not recognised by our reporting application (Crystal Reports), we use a function to convert the tags to a set that are recognised. e.g.

The html tag '<strong>' is not recognised, so we convert it to '<b>', using the function:

CREATE OR REPLACE FUNCTION Handle_Clob (p_string IN clob)
  RETURN clob
IS

BEGIN
  RETURN replace(replace(replace(replace(replace(p_string, '<p>', '<p><p>'),'</p>','</p></p>' ),'strong>','b>'),'em>','i>'), 'sub>', 'small>');
END;
/

This will turn this...

 <P><STRONG>B.3.11 Stock Solution Preparation</STRONG></P>

...into this

 <p><p><b>b.3.11 stock solution preparation</b></p></p>

i.e., Unfortunately, whilst the HTML tags are then recognised, this has the side effect of making all of the text lower case.

Does anyone know why this might be?

Thanks, Gavin

 

[carp]

I'm surprised it does anything at all!
REPLACE('<P>Anything</P>','<p>','<p><p>')
should yield '<P>Anything</P>', since there are no lower case '<p>'s present.
Are you sure you aren't casting things to lower case upstream of handle_clob?

 

[gav12345]

D'oh, sorry, I was looking at the wrong function. - You're right, the above function wouldn't do much at all. FYI, the correct code is (I will just remove the 'lower' and add upper case <P>'s etc.) Thanks, Gavin

CREATE OR REPLACE FUNCTION Handle_Varchar (p_string IN varchar2)
  RETURN varchar2
IS

BEGIN
  RETURN replace(replace(replace(replace(replace(lower(p_string), '<p>', '<p><p>'),'</p>','</p></p>' ),'strong>','b>'),'em>','i>'), 'sub>', 'small>');
END;
/

 

[carp]

I think
lower(p_string)
is your problem. You are converting everything to lower case, doing your replacements, and returning the lower case results.

 

[gav12345]

Thanks carp.