Reading rtf from an image field

Hi,

Tasks in a MS Project SQL Server database have an image field called
TASK_RTF_NOTES. It contains text in RTF format. We would like to convert
this within a standard SQL statement into plain text.

I suspect this is not possible without some sort of bolt-on, or at the very
least using T-SQL in some way.

Any ideas?

Thanks,

Gareth

re: Reading rtf from an image field

"Gareth Thackeray" <firstname.lastname@logicacmg.com> wrote in message
news:1089045508.644762@ernani.logica.co.uk...[color=blue]
> Hi,
>
> Tasks in a MS Project SQL Server database have an image field called
> TASK_RTF_NOTES. It contains text in RTF format. We would like to convert
> this within a standard SQL statement into plain text.
>
> I suspect this is not possible without some sort of bolt-on, or at the[/color]
very[color=blue]
> least using T-SQL in some way.
>
> Any ideas?
>
> Thanks,
>
> Gareth
>
>
>[/color]

SQL is not a good language for text processing - a better approach would be
to use an external script or program to read out the RTF data, convert it,
then UPDATE the row with the new plain text data.

In theory, you might be able to write something in TSQL using REPLACE() to
remove all the RTF metadata strings, but it would be much easier to look for
an existing tool or piece of code in your preferred language (Perl, VB, C#
etc), then write a simple script to do this.

Simon

re: Reading rtf from an image field

Gareth Thackeray (firstname.lastname@logicacmg.com) writes:[color=blue]
> Tasks in a MS Project SQL Server database have an image field called
> TASK_RTF_NOTES. It contains text in RTF format. We would like to convert
> this within a standard SQL statement into plain text.
>
> I suspect this is not possible without some sort of bolt-on, or at the
> very least using T-SQL in some way.[/color]

I don't know RTF very well, but from what I have seen of it, I certainly
would not try to extract the plain text using T-SQL. Sure, you can use
a screwdriver to drive a nail if you try real hard, but why?`

Possibly in the next version of SQL Server where you can call CLR directly
in SQL Server it could be worth trying.

But for now, do as Simon says and get the data to the client-side and
try something there. (For instance, open the document from Word as
RTF and save as plain text, using a Word-macto. Why reinvent wheels?)


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

re: Reading rtf from an image field

"Erland Sommarskog" <esquel@sommarskog.se> wrote in message
news:Xns951DF058DE875Yazorman@127.0.0.1...[color=blue]
> Gareth Thackeray (firstname.lastname@logicacmg.com) writes:[color=green]
> > Tasks in a MS Project SQL Server database have an image field called
> > TASK_RTF_NOTES. It contains text in RTF format. We would like to[/color][/color]
convert[color=blue][color=green]
> > this within a standard SQL statement into plain text.
> >
> > I suspect this is not possible without some sort of bolt-on, or at the
> > very least using T-SQL in some way.[/color]
>
> I don't know RTF very well, but from what I have seen of it, I certainly
> would not try to extract the plain text using T-SQL. Sure, you can use
> a screwdriver to drive a nail if you try real hard, but why?`
>
> Possibly in the next version of SQL Server where you can call CLR directly
> in SQL Server it could be worth trying.
>
> But for now, do as Simon says and get the data to the client-side and
> try something there. (For instance, open the document from Word as
> RTF and save as plain text, using a Word-macto. Why reinvent wheels?)[/color]

Thankyou both for your advice.

FYI, the reason for trying the route we are trying is that we are
integrating into Remedy ARS, and the built-in database integration only
allows views and tables to provide the data for a "View Form".

Furthermore, it is not permitted to add an image (or text...) field to the
form, so we would need to convert it at the SQL level.

I had thought it was probably a long shot but you don't get if you don't
ask..

Thanks again,

Gareth

re: Reading rtf from an image field

Gareth Thackeray (firstname.lastname@logicacmg.com) writes:[color=blue]
> FYI, the reason for trying the route we are trying is that we are
> integrating into Remedy ARS, and the built-in database integration only
> allows views and tables to provide the data for a "View Form".
>
> Furthermore, it is not permitted to add an image (or text...) field to the
> form, so we would need to convert it at the SQL level.[/color]

You could have a separate job that reformats the RTF stuff into something
presentable. But if you cannot present text column in Remedy, you are pretty
lost anyway, unless you can accept to view the first 8000 chars only.

I have never heard of Remedy ARS, but I suppose there is a vendor. It may
be worth contact them and ask for suggestions.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp