Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations SkipVought on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Convert/Remove RTF text info from SQL text field 1

Status
Not open for further replies.
nicoh99

nicoh99

Programmer
Mar 16, 2009
17
0
0
US
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
 
Sort by date Sort by votes
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:


You may also find this useful:


Good luck.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom
 
Upvote 0 Downvote
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


 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

gbaughma
  • Locked
  • Question
SQL Performance
Replies
11
Views
166
pjw001
pjw001
dgillz
  • Locked
  • Question
How to convert varchar to time? 2
Replies
1
Views
207
Chris Miller
Chris Miller
peac3
  • Locked
  • Question
convert hierarchy structure to parent children pair please... 1
2
Replies
22
Views
284
mikrom
mikrom
bharons
  • Question
Pick up value from combobox
Replies
0
Views
141
bharons
bharons
grnzbra
  • Locked
  • Question
Adding Text to and XML Field 1
Replies
2
Views
76
grnzbra
grnzbra

Part and Inventory Search

Sponsor

Back
Top