remove control codes from database

I have control codes in the description field as shown below. I need to
create a report of only code and description without the \rtf... etc
Is there any way I can remove these from my final output using SQL?
Any help is greatly appreciated.
modcode longdescription
lastupdate
updateid
--
----
----
----
--
-
--- --
1 {\rtf1\ansi\deff0{\fonttbl{\f0\fnil\fcha
rset0 MS Sans Serif;}}
\viewkind4\uc1\pard\lang1033\f0\fs17 A normal healthy patient
\par }Could you post a sample of what you want your data to look like?
"mcp111" wrote:

> I have control codes in the description field as shown below. I need to
> create a report of only code and description without the \rtf... etc
> Is there any way I can remove these from my final output using SQL?
> Any help is greatly appreciated.
> modcode longdescription
>
> lastupdate
> updateid
> --
> ----
----
----
--
--
> --- --
> 1 {\rtf1\ansi\deff0{\fonttbl{\f0\fnil\fcha
rset0 MS Sans Serif;}}
> \viewkind4\uc1\pard\lang1033\f0\fs17 A normal healthy patient
> \par }
>|||mcp111
I used a UDF to strip out the rtf tags.
The UDF is shown below..
BE WARNED ! this is a rough and ready fix for a problem I had and will NOT
cope with all posible rft tags but it may be useful as start for you.
Cheers
David.
---
CREATE FUNCTION Strip_RTF_Tags ( @.LineIn Varchar(4000)
)
RETURNS Varchar(4000)
AS
BEGIN
DECLARE @.BraceCount Int
DECLARE @.StartCount Int
DECLARE @.EndCount Int
DECLARE @.LoopCount Int
declare @.InText Int
DECLARE @.S varchar(4000)
DECLARE @.P1 Int
SET @.BraceCount = 0
SET @.StartCount = 0
SET @.EndCount = 0
SET @.LoopCount = 1
SET @.S = ''
SET @.InText = 0
WHILE @.LoopCount <= Len(@.LineIn)
BEGIN
If SUBSTRING(@.LineIn, @.LoopCount, 1) = '{'
BEGIN
SET @.BraceCount = @.BraceCount + 1
END
If SUBSTRING(@.LineIn, @.LoopCount, 1) = '}'
BEGIN
SET @.BraceCount = @.BraceCount - 1
END
If (SUBSTRING(@.LineIn, @.LoopCount, 1) = ' ')
AND (@.BraceCount = 1)
AND (@.InText = 0)
BEGIN
SET @.StartCount = @.LoopCount +1
SET @.InText = 1
END
If (SUBSTRING(@.LineIn, @.LoopCount, 1) = '')
AND (@.BraceCount = 1)
AND (@.InText = 1)
BEGIN
SET @.EndCount = @.LoopCount
SET @.S = @.S + SUBSTRING(@.LineIn, @.StartCount, @.EndCount -
@.StartCount)
SET @.InText = 0
END
SET @.LoopCount = @.LoopCount + 1
END
-- SET @.S = Cast(@.Startcount as varchar) + ' '+ Cast(@.EndCount as
varchar)
Return @.S
END
---
"mcp111" <mcp111@.discussions.microsoft.com> wrote in message
news:960CBFF0-80B1-4E25-9010-9F14C8CE8C6D@.microsoft.com...
>I have control codes in the description field as shown below. I need to
> create a report of only code and description without the \rtf... etc
> Is there any way I can remove these from my final output using SQL?
> Any help is greatly appreciated.
> modcode longdescription
>
> lastupdate
> updateid
> --
> ----
----
----
--
--
> --- --
> 1 {\rtf1\ansi\deff0{\fonttbl{\f0\fnil\fcha
rset0 MS Sans Serif;}}
> \viewkind4\uc1\pard\lang1033\f0\fs17 A normal healthy patient
> \par }
>|||Your best bet is an application dedicated to stripping RTF codes from RTF
data. SQL Server doesn't know anything about RTF, and in order to have SQL
Server do this, you'd have to re-invent the wheel...
"mcp111" <mcp111@.discussions.microsoft.com> wrote in message
news:960CBFF0-80B1-4E25-9010-9F14C8CE8C6D@.microsoft.com...
>I have control codes in the description field as shown below. I need to
> create a report of only code and description without the \rtf... etc
> Is there any way I can remove these from my final output using SQL?
> Any help is greatly appreciated.
> modcode longdescription
>
> lastupdate
> updateid
> --
> ----
----
----
--
--
> --- --
> 1 {\rtf1\ansi\deff0{\fonttbl{\f0\fnil\fcha
rset0 MS Sans Serif;}}
> \viewkind4\uc1\pard\lang1033\f0\fs17 A normal healthy patient
> \par }
>sql