November 21, 2004 at 4:26 pm
I have a column in SQL which stores project descriptions with html before and after the text. We export the descriptions to Access using a DTS package (copy column.) I need to know how to use ActiveX or VB, etc. to either convert to text before it goes into Access or filtering the html code out might also work. I have not had any success trying either of these. Would it be best to convert to text or to try and filter the html code out? I'm relatively new to DTS and am unsure of where in the transformation step to insert the appropriate code (if I have it.) For example - before or after the existing "copy colum" task, etc.
Thanks!
November 22, 2004 at 6:02 am
It sounds like you could use regular expressions to strip out the html;
on codeproject.com (http://www.codeproject.com/managedcpp/xpregex.asp) there is an extended stored procedure which is basically a wrapper around one of the programming language Regular Expressions Library; it requires SQL 2000 and the .NET framework 1.1 installed on the server to support the xp_, but it works very very well if oyu have a need for using a lot of regular expressions.
you could then use that xtended proc to strip out your html and return just the text. as part of a DTS or to update a column.
Lowell
November 22, 2004 at 6:10 am
Actually, the best thing to do is make the adjustment in Access. Since the HTML code can occur any where in the text, you will have to write a rather extensive routine to do the parsing. Or, write a stored procedure to load the text into a different field, parsing it with the stored procedure.
November 22, 2004 at 10:08 am
In this particular case, the html code is before the text we need and is the same string for each field. This might be as simple as a "trim left" command in the data transformation task. Can someone show me the correct way to use this command? We recently changed database structures and the project description field used to be stored as text only. Now it contains the html code in SQL. I am also receiving buffer overrun errors when I try to export to Access. I tried boosting the buffer at the ODBC source, but that did not work. The html code we want to remove is only about 100 characters long. Why would adding only this much data to each project description cause a buffer overrun? It runs fine as long as we're only exporting the text. Thanks a lot for the help!
November 22, 2004 at 12:26 pm
Send me the first 200 characters with about 10 records and I will send you the code to do the parsing. rl_stewart@highstream.net
November 23, 2004 at 8:38 am
I pointed him to the CHARINDEX and SUBSTRING functions.
CHARINDEX allows him to find the starting character number of a character or string inside of a string.
SUBSTRING allows for the parsing of a string and returning a portion of it.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply