Convert/Remove RTF text info from SQL text field 1

[nicoh99]

Hi guys,

(This is somewhat of a revamped version of a previous question I posted. I had mistaken the RTF formatting as something to do with Collation)

I have a text field(notes) in SQL Server that is storing the data with all of the rtf formats.

{\rtf1\ansi\deff0{\fonttbl{\f0\fnil\fcharset0 MS Shell Dlg 2;}}
\viewkind4\uc1\pard\lang1033\f0\fs17 test comments are here

I need to pull the notes into an Excel spreadsheet without all of the rtf info (I want to end up with "test comments are here" from the above example), but haven't had much luck. I tried using the Mid function when getting the data over to excel to omit this data, but it is not consistently the same length throughout all of my records. I don't think a substring function in my SQL statement would work either because I the rtf data doesn't always ends with "fs17". Is there a way to strip out/convert the rtf info in a SQL query ? If not, is there a way to do this once I get the data into Excel/VBA code?
Any ideas for a solution would be appreciated. I have googled my rear off this morning and haven't found much that will work =-(
Thanks!

Nic

 

[gmmastros]

My suggestion would be to accomplish this in your front end. I did a quick google search and there appears to be some regex code that claims to remove RTF formatting. You can do regex stuff in T-SQL, but it's slow and requires elevated permissions. I'm not too familiar with Excel, but I think you can....

Attach to a SQL Database, pull data from it, and run REGEX code to strip the formatting.

A quick google search on "Remove RTF Formatting" shows this:

http://www.codeguru.com/forum/showthread.php?t=317279

You may also find this useful:

http://www.tmehta.com/regexp/add_code.htm

Good luck.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[nicoh99]

George,

Thanks for pointing me in the right direction. I was getting close in SQL server using the following in my column field:

REPLACE(REPLACE(REPLACE(SUBSTRING(CONVERT (VARCHAR(400), dbo.EmployeeNotes.Notes), 101, 100), '\par }', ''), CHAR(10), ''), CHAR(13), '')

I don't think this was going to work 100% though.
I had never heard of REGEX before and was doubting I'd be able to figure it out, but it looks like it is going to work! I now just need to clear out a couple misc } symbols and chr(10)/chr(13) boxes.
Thanks again for taking the time to help!

-Nic