Re: Upper, Longvarchar and RTF text

Comments embedded.
On Jun 12, 4:56 am, Sonnich Jensen <sonnich.jen..._at_jotautomation.com> wrote:
> Hi!
>
> I have this: select * from blaaah and upper(somefield) like '%EPLACED
> %'

I'm hoping this is a typographical error and you simply missed pasting the WHERE clause ...

>
> somefield is of type LongVarchar, and contains text in RTF format.
> We are looking for the text "Replaced", which in most cases is "R\f1
> eplaced".

My guess is this is a LONG datatype, as LongVarchar does not exist as a type.

>
> The upper should help us out, for both "upper" and "like" I get an
> errors: expexted NUMBER got LONG.
> It is a longvarchar.
> Even select upper( the field) will cause that problem.

Yes, LONG does not behave as one might expect given the data it contains. You'll need to convert this text to a varchar2 before you can use upper() or
substr(). I would be looking into writing some PL/SQL code to do this. If I remember correctly asktom.oracle.com has examples of this.

David Fitzjarrell
>
> The text looks like eg:
> {\rtf1\ansi\deff0{\fonttbl{\f0\fnil\fcharset0 Arial;}{\f1\fnil
> \fcharset186{\*\fname Arial;}Arial Baltic;}{\f2\fnil Arial;}}
> \viewkind4\uc1\pard\lang1061\b\fs20 R\f1 EPLACED WITH ITEM xxxxxxx
> \f2
> \par }
>
> Any ideas to overcome this?
>
> BR
> Sonnich
Received on Tue Jun 12 2007 - 08:41:13 CDT