Using RegEx to remove RTF formatting

Transact-SQL
1

Hi

My database contains some fields that are are TEXT but the data stored includes all the rtf formatting tags (the database uses a text editor for staff to type in notes) and I need to report the text only, not all the formatting tags.

I am certain I need to use a RegEx Replace function, however I have never used one before and can't understand how to use it (despite googling intensively).

The problem is that the field does not always include the same codes, as can be seen from the examples below. I have downloaded and run the free version of SQL# as one of the articles mentioned it was a good one to use, but again, without understanding the syntax, it is hard for me to understand what to do.

I do need to keep the paragraph spaces (not sure how to do this) as otherwise it will all be one garbled message, and this report is used to review the incidents, and they need to read the detail.

Hopefully someone can help.

Example 1:

{\rtf1\ansi\deff0\uc1\ansicpg1252\deftab720{\fonttbl{\f0\fnil\fcharset1 Arial;}{\f1\fnil\fcharset2 Wingdings;}{\f2\fnil\fcharset2 Symbol;}}{\colortbl\red0\green0\blue0;\red255\green0\blue0;\red0\green128\blue0;\red0\green0\blue255;\red255\green255\blue0;\red255\green0\blue255;\red128\green0\blue128;\red128\green0\blue0;\red0\green255\blue0;\red0\green255\blue255;\red0\green128\blue128;\red0\green0\blue128;\red255\green255\blue255;\red192\green192\blue192;\red128\green128\blue128;\red0\green0\blue0;}\wpprheadfoot1\paperw12240\paperh15840\margl1880\margr1880\margt1440\margb1440\headery720\footery720\endnhere\sectdefaultcl{\*\generator WPTools_7.000;}{\plain\f0\fs22 New Note Added by: PH - 31/05/2015 7:46:50 p.m.\par
\plain\f0\fs22\line\fs24 Customer was walking down to the toilet to get change into her PJ, when I was in the Bathroom doing a care I heard Customer walking then a loud bang I pop my head to see what was that, saw Customer on her hands and knees outside her room on the floor went over to check Customer she said she's ok.  But she might have some bruise sometime during the week, called 0800BEHAVE , Customer seem to be walking fine not complaining about any pain \par
\pard\plain\par
\pard\plain\par
}}

Example Two:

    {\rtf1\ansi\deff0\uc1\ansicpg1252\deftab720{\fonttbl{\f0\fnil\fcharset1 Arial;}{\f1\fnil\fcharset2 Wingdings;}{\f2\fnil\fcharset2 Symbol;}}{\colortbl\red0\green0\blue0;\red255\green0\blue0;\red0\green128\blue0;\red0\green0\blue255;\red255\green255\blue0;\red255\green0\blue255;\red128\green0\blue128;\red128\green0\blue0;\red0\green255\blue0;\red0\green255\blue255;\red0\green128\blue128;\red0\green0\blue128;\red255\green255\blue255;\red192\green192\blue192;\red128\green128\blue128;\red0\green0\blue0;}\wpprheadfoot1\paperw12240\paperh15840\margl1880\margr1880\margt1440\margb1440\headery720\footery720\endnhere\sectdefaultcl{\*\generator WPTools_7.000;}{\plain\fs22 New Note Added by: JP - 04/05/2015 2:23:29 p.m.\par
\plain\fs22\line\fs24 Customer arrived on base and picked up PC JP and headed off to H20 for a swim.  We used the special needs changing room.  Customer struck out a couple of times but was easily redirected, changed and into the swimming pool\par
\pard\plain\plain\fs24\par
\plain\fs24 Customer sat by the water fall and played with the water.  he had fun kicking and splashing.  he enjoyed himself with smiles and laughs.  he engaged with CSW's with some wrist grabbing and scratching as per normal.\par
\pard\plain\plain\fs24\par
\plain\fs24 Customer enjoyed a walk around the river run and deeper water.  He got CSW's to lift him up and down in the water, lots of splashing, smiles and laughs.  Next Customer went into the spa.  He was relaxed alternating between sitting and learning on the side.  Some of the regulars recognised him and said hello.\par
\pard\plain\plain\fs24\par
\plain\fs24 Customer was friendly unless approached then he would grab people hands and scratch at them.  Customer can be difficult for people to read as he would put his hand out as if to shake and say hello but then sometimes grab and scratch at people.\par
\pard\plain\plain\fs24\par
\plain\fs24 It was about 11am when Customer left the spa.  When asked Customer said no to the sauna.  Then Customer started hitting lockers and out at people.  We felt that maybe he had had enough swimming for the day and he was directed to the changing room.  He was happy to grab his bags and change.\par
\pard\plain\plain\fs24\par
\plain\fs24 After changing he was reluctant to leave the changing area.  He was distracted with some cloths shuffling and he got up to go the car.\par
\pard\plain\plain\fs24\par
\plain\fs24 Once in the car Customer became highly elevated.  He attacked CSW's when they tried to put on his seat belt.  Tow CSW's were needed , one to distract the other to click the seat belt in place.\par
\pard\plain\plain\fs24\par
\plain\fs24 Customer then attacked his CSW when he tried to get into the drivers seat.  Customer was given some time to settle and CSW SM tried again, this was repeated a couple more times.  Next PC JP tried and Customer attacked him too. On the second attempt PC JP started the car and and headed off.  \par
\pard\plain\plain\fs24\par
\plain\fs24 We stopped 10 minutes down the road to change drives but Customer attacked CSW SM again.  We gave Customer a few minutes to settle then PC JP drove Customer back to the base.  CSW SM then could drive Customer home.  Written on behalf of JP(PC)\par
\pard\plain\plain\fs22\par
\pard\plain\plain\fs22\par
\pard\plain\plain\fs22\par
}}

Example 3:

{\rtf1\ansi\deff0\uc1\ansicpg1252\deftab720{\fonttbl{\f0\fnil\fcharset1 Arial;}{\f1\fnil\fcharset2 Wingdings;}{\f2\fnil\fcharset2 Symbol;}}{\colortbl\red0\green0\blue0;\red255\green0\blue0;\red0\green128\blue0;\red0\green0\blue255;\red255\green255\blue0;\red255\green0\blue255;\red128\green0\blue128;\red128\green0\blue0;\red0\green255\blue0;\red0\green255\blue255;\red0\green128\blue128;\red0\green0\blue128;\red255\green255\blue255;\red192\green192\blue192;\red128\green128\blue128;\red0\green0\blue0;}\wpprheadfoot1\paperw12240\paperh15840\margl1880\margr1880\margt1440\margb1440\headery720\footery720\endnhere\sectdefaultcl{\*\generator WPTools_7.000;}{\plain\fs22 New Note Added by: JP - 21/05/2015 3:43:05 p.m.\par
\plain\fs22\line\fs24 On leaving the Mall Customer when to his car in the car park.  On nearing the car he refused to get in and leaned on the car next to his.  Customer was encouraged to get in his car but continued to refuse, till the ower of the car came over.  The car owner started their car and Customer moved away to lean on a pole beside his car.  As the car was pulling out PC JP became concerned that Customer feet could get run over, so he move forward and pulled Customer aside. \par
\pard\plain\plain\fs24\par
\plain\fs24 Customer became more distressed and grabbed hold of his leg, trying to scratch and to bite him.  Customer got a graze on his face, from rubbing his head up and down JP knee and calf area.  CSW DP, went to assist JP but he was told to wait, as Customer was not able to hurt his leg.  And when Customer stoped thrashing about, JP got him to let go, and moved away.  Customer calmed down as long as no one came near him.\par
\pard\plain\plain\fs24\par
\plain\fs24 Customer car was moved to park in front of Customer as there were lots of vehicles in the Mall, and they would try to park in the space Customer was sitting in, we did redirect traffic way but felt this was a more practical solution to traffic.  \par
\pard\plain\plain\fs24\par
\plain\fs24 When it was realised that Customer would not follow any of our direction to get into his car.  TL GV was called that we would be delayed, and that at this stage we were giving him more time to setting.    GV suggested a change of face strategy, and sent over DV.  \par
\pard\plain\plain\fs24\par
\plain\fs24 DV and GV arrived in the van and DV attempted to encourage Customer to get in his car, he refused.  We tried a lifting strategy but Customer just became more upset.\par
\pard\plain\plain\fs24\par
\plain\fs24 Several people(General Public) came over concerned with helping with the situation.  But were informed Customer was just distressed at the moment, and would be ok.  One lady, in the next car over, became upset and GV took time to talk to her and explain the situation.\par
\pard\plain\plain\fs24\par
\plain\fs24 Next GV call Manager EN and On Call, but they were unfortunately not available at that time and sent straight through to answer phone.  Then TL DW call GV and GV explained the situation.  DW said that she was ok with what we had done so far that that we could do what we needed to do to keep everybody safe.  GV said that she was now looking at calling the police as the next step. \par
\pard\plain\plain\fs24\par
\plain\fs24 Fortunately then JP opened the van door and asked Customer if he wanted a ride in the van rather than the car.  Customer allowed JP to get him up.  Then Customer changed his mind and went to ground again.  JP believed that Customer did want to go in the van and tried again.  Customer got into the van and was seat belted in.\par
\pard\plain\plain\fs24\par
\plain\fs24 DP stated that he was not happy to travel back in the car with Customer so we decided to take him straight home in the van.  JP and DV drove Customer home, with DP driving back Customer's car.  Customer was calm on the ride home.\par
\pard\plain\plain\fs24\par
\plain\fs24 When we got back to Customer house, he got out of the van, but as we could not park in his garage he went over to his yard fence and refused to move from there.  He could be approached but refused to move.\par
\pard\plain\plain\fs24\par
\plain\fs24 We moved van way, made several attempts to get him into his house.\par
\pard\plain\plain\fs24\par
\plain\fs24 PM came over and tried to encourage him as well, with chocolate and his favourite lawn mower.  He refused.  PM went to see if any of her family was close.\par
\pard\plain\plain\fs24\par
\plain\fs24 GV was called to inform her of this delay.  DW talked to DP about the situation and then he informed us that it was ok for JP and DV to leave, were concerned that DP was becoming stressed.  DP said he could manage Customer as he was home.   \par
\pard\plain\plain\fs24\par
\plain\fs24 JP tried one more time and convinced Customer to go upstairs an into the house.\par
\pard\plain\plain\fs22\par
\pard\plain\plain\fs22\par
}}
2

I have tried this just to get out one of the common lines '\pard\plain\plain\fs24\par' using this syntax, that I think is right:

use [exoMashTrust-test]

select 
	sql#.RegEx_Replace(
	pati.THENOTES
	,'\pard\plain\plain\fs24\par'
	,''
	,1
	,1
	,null
	)

from
 PATIENT_INCIDENTS as pati

But I get this error:

Msg 6522, Level 16, State 1, Line 3
A .NET Framework error occurred during execution of user-defined routine or aggregate "RegEx_Replace": 
System.ArgumentException: parsing "\pard\plain\plain\fs24\par " - Malformed \p{X} character escape.
System.ArgumentException: 
   at System.Text.RegularExpressions.RegexParser.ParseProperty()
   at System.Text.RegularExpressions.RegexParser.ScanBackslash()
   at System.Text.RegularExpressions.RegexParser.ScanRegex()
   at System.Text.RegularExpressions.RegexParser.Parse(String re, RegexOptions op)
   at System.Text.RegularExpressions.Regex..ctor(String pattern, RegexOptions options, TimeSpan matchTimeout, Boolean useCache)
   at System.Text.RegularExpressions.Regex..ctor(String pattern, RegexOptions options)
   at REGEX.Replace(SqlChars ExpressionToValidate, SqlString RegularExpression, SqlString Replacement, SqlInt32 Count, SqlInt32 StartAt, SqlString RegExOptionsList)
.

I have no idea what this means.

4

I am very grateful you have some experience with Regular Expressions and have replied to this post.

This is my script now:

use [exoMashTrust-test]
select 
	sql#.RegEx_Replace(
		pati.THENOTES
		,'\pard\plain\plain\fs24\par'
		,'  '
		,1
		,1
		,null)
from
 PATIENT_INCIDENTS as pati
where 
pati.SEQNO in (3576,3575,3574,3573)

I limited the changes to certain ID's so I could check them more easily.

But I get this error:

Msg 6522, Level 16, State 1, Line 3
A .NET Framework error occurred during execution of user-defined routine or aggregate "RegEx_Replace": 
System.ArgumentException: parsing "\\pard\plain\plain\fs24\par" - Malformed \p{X} character escape.
System.ArgumentException: 
   at System.Text.RegularExpressions.RegexParser.ParseProperty()
   at System.Text.RegularExpressions.RegexParser.ScanBackslash()
   at System.Text.RegularExpressions.RegexParser.ScanRegex()
   at System.Text.RegularExpressions.RegexParser.Parse(String re, RegexOptions op)
   at System.Text.RegularExpressions.Regex..ctor(String pattern, RegexOptions options, TimeSpan matchTimeout, Boolean useCache)
   at System.Text.RegularExpressions.Regex..ctor(String pattern, RegexOptions options)
   at REGEX.Replace(SqlChars ExpressionToValidate, SqlString RegularExpression, SqlString Replacement, SqlInt32 Count, SqlInt32 StartAt, SqlString RegExOptionsList)
.

This is the same error. And I think it's because the \rtf contains characters which are special characters in Regular Expressions.

What I want to do is a find and replace only on the the rtf that matches my string, not using any special characters or anything (that the multiple rtf characters are doubtless creating an issue with) such as { and .

Is that possible?