Re: Query inside RTF

On jue, 2009-10-08 at 11:28 -0400, Merlin Moncure wrote:
> 2009/10/8 Leonardo M. <l(dot)rame(at)griensu(dot)com>:
> > Hi, in my database I store RTF files inside a Bytea field. Now a
> > customer is requesting a search inside RTF fields and I'm trying to
> > implement it by issuing this query:
> >
> > select
> > *
> > from my_table
> > where
> > cast(rtf_field as varchar) like '%condition%'
> >
> > This works ok when the condition doesn't include accented chars. RTF
> > escapes accented characters as "\'f1" for í, "\'f3" for ó, and so on.
> >
> > To escape \ and ', I'd read it shuld be used \\ and '', so I thaugth
> > that a like '%diagn\\\\''f3stica%' should get "diagnóstica", but it
> > doesn't.
> >
> > Any hint on this?
>
> first, try dollar quoting:
> cast(rtf_field as varchar) like $merlin$%condition%$merlin$
>
> :-)
>
> merlin
>

Thanks Merlin, I found the solution, it was related to
"standard_conforming_strings". My query is this now:

set standard_conforming_strings = 1;

SELECT
idturno,
Upper(cast(InfRes as Varchar))
as InfRes
from turno
where
cast(InfRes as Varchar) like '%diagn\\\\''f3stica%';

--
Leonardo M. Ramé
Griensu S.A. - Medical IT Córdoba
Tel.: 0351-4247979