Avatar of incrediblejohn
incrediblejohn
 asked on

Need Regular Expression to strip RTF off of field value

We have SQL Server storing rtf formatting in the form of text string. For quick queries, reports, we don't want to use an rtf control, just strip off the formatting and show the text. I can't seem to get the Regular Expression correct to do this:

Dim buf2 As String
buf2 = "{\rtf1\ansi\deff0{\fonttbl{\f0\froman Tms Rmn;}{\f1\froman Times New Roman;}}"
buf2 = buf2 & "{\colortbl\red0\green0\blue0;\red0\green0\blue255;\red0\green255\blue255;"
buf2 = buf2 & "\red0\green255\blue0;\red255\green0\blue255;\red255\green0\blue0;\red255"
buf2 = buf2 & "\green255\blue0;\red255\green255\blue255;\red0\green0\blue127;\red0\green127\"
buf2 = buf2 & "blue127;\red0\green127\blue0;\red127\green0\blue127;\red127\green0\blue0;\red127\"
buf2 = buf2 & "green127\blue0;\red127\green127\blue127;\red192\green192\blue192}{\info"
buf2 = buf2 & "{\creatim\yr2004\mo6\dy28\hr10\min41\sec1}{\version1}{\vern262367}}\"
buf2 = buf2 & "paperw12240\paperh15840\margl360\margr0\margt239\margb0\deftab720\pard\ql"
buf2 = buf2 & "{\f1\fs20\cf0\up0\dn0 06/28/04 Goldie nds lic# and original date of iss liz}"
buf2 = buf2 & "{\par}\pard\ql{\f1\fs20\cf0\up0\dn0 PerRem|??:|  EmplStat|EPART-OTH|}{\par}\pard\ql}"

Dim regexp As VBScript_RegExp_55.regexp
Set regexp = New VBScript_RegExp_55.regexp
regexp.Global = True
regexp.IgnoreCase = True
Dim buf As String

regexp.Pattern = "(\{\\f\d*)\\([^;]+;)"

Dim str As String

str = regexp.Replace(buf2, "")

MsgBox str
Microsoft Access

Avatar of undefined
Last Comment
Gustav Brock

8/22/2022 - Mon
Scott McDaniel (EE MVE )

The Perl or Unix guys would probably be more help with this than us database hacks ... I've played with reg exps but never really got my head around them ... maybe put a 20 point pointer question in one of those TAs over to this.
ASKER CERTIFIED SOLUTION
Gustav Brock

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
incrediblejohn

ASKER
Great answer! Worked like a charm and really fast! I'm a hero here at work now.
Gustav Brock

A hero? Fine!

/gustav
Your help has saved me hundreds of hours of internet surfing.
fblack61