Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2002
    Location
    KY
    Posts
    56

    Question Unanswered: T-SQL to strip HTML tags from string without using a function...need straight SQL

    Can someone assist me with straight t-sql to strip HTML tags from a string in a column field. will need all variations of HTML tags stripped before exporting to a flat file.
    The application sql window does not allow me to create functions since it was created by a third party vendor, but I have access to pull raw data using Putty.

    here's an example of a record with HTML tabs:
    "<P class=MsoNormal style=""MARGIN: 0in 0in 0pt""><FONT size=3><FONT face=""Times New Roman""><I><U>Watch Hit Information:</U></I><U> </U><?xml:namespace prefix = ""o"" ns = ""urn:schemas-microsoft-comfficeffice"" /><o></o></FONT></FONT></P>
    <TABLE class=MsoNormalTable style=""mso-cellspacing: 1.5pt; mso-yfti-tbllook: 1184"" cellPadding=0 border=0>
    <TBODY>
    <TR style=""mso-yfti-irow: 0; mso-yfti-firstrow: yes"">
    <TD style=""BORDER-TOP: #f0f0f0; BORDER-RIGHT: #f0f0f0; BORDER-BOTTOM: #f0f0f0; PADDIN"

    If this data was all in SQL Server I'd have no problems stripping HTML tags with a function, but since its DB2...its a whole new ball game.

    Thanks in advance for your assistance

  2. #2
    Join Date
    Apr 2012
    Posts
    1,143
    Provided Answers: 27
    May be more efficient to export to flat file and use text tools to process the HTML instead of using SQL.

    DB2 does not use t-sql, it uses either ANSI SQL PL or Oracle-syle PL/SQL emulation if enabled.

    If you have PUTTY access to the DB2-server, then what is stopping you creating an SQL-function from the command-line?
    Otherwise you need to specify your DB2-server *VERSION* and operating-system platform/version for the DB2-server because that will determine which features are available.

  3. #3
    Join Date
    Dec 2002
    Location
    KY
    Posts
    56
    The export is an automated job that executes nightly along with 265 other tables...this one particular flat file is causing the SQL Job in SQL Server to error out during the bulk insert do to the HTML tags...

    Db2 v 10.5
    AIX v 6, we're preparing for v 7

    Let me ask this...once all tables are exported to text files...is it possible to run this command against that flat file to strip all html tags during the nightly job: sed -n '/^$/!{s/<[^>]*>//g;p;}' filename

  4. #4
    Join Date
    Apr 2012
    Posts
    1,143
    Provided Answers: 27
    The only person who can verify the eventual outcome of sed script for your data is you and whatever business users need to use the data in that column when loaded into SQL-server tables.

    Did this work correctly in the past, and just recently the bulk-insert from flat-file into SQL-Server failed with some unspecified error.

    If this is a production system and either this bulk-insert into SQL-server never worked before, or if this is a new table or new column, then maybe you need to fix the underlying issue before changing the data, which may carry risk. You might want to get approval from the data owner that altering the data is allowed.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •