SQL Server- Export Data without html formatting

• marialena.webdesign

  Valued Member

  Points: 67

  More actions

  October 22, 2015 at 8:27 am

  #312439

  I need to export some Database data into a text file. My Query looks like this:

  SELECT Category1, Category2, Category3

  FROM dbo.tbl1

  WHERE Category1 = 'JP-4'

  AND Category2> 4;

  This works fine to get the data, however there is some html formatting in the table entries such as

  `<p>,</p>,

  ,</br>` etc. So ideally I need to remove those when exporting the data to the text file. I've tried to do it with a simple replace query but that didn't work.

  I've also got an issue with line splits and would need to remove the (\r).

  Any suggestions on how to do this would be really appreciated!

  The Data format is something like this:

  Category1: JP-4

  Category2: 4

  Category3:<p>Neque porro quisquam est qui dolorem ipsum quia dolor</p> <p>amet, consectetur, adipisci velit</p>

  Category4:<p>Neque porro quisquam est qui dolorem ipsum quia dolor</p>

  I got it to work like this with the replace function:

  SELECT REPLACE(REPLACE("PHOTOGRAPHS",'<p>',''),'</p>','')

  FROM dbo.khia_tbl

  WHERE Category1= 'JP-4'

  AND Category2> 4;

  but the issue is that I've got 15 columns in total and that I need to do it for several different tags for each column so

  ,

  </br>,

  as well as "" and different spaces so that would be a lot and I thought there must be a better/more efficient way of doing it

• Michael L John

  One Orange Chip

  Points: 27079

  More actions

  October 22, 2015 at 8:38 am

  #1834921

  This function was posted someplace that i can't remember. Don't tell Moden is uses a loop though!

  CREATE FUNCTION [dbo].[udf_StripHTML]

  (@HTMLText VARCHAR(MAX))

  RETURNS VARCHAR(MAX)

  AS

  BEGIN

  DECLARE @Start INT

  DECLARE @End INT

  DECLARE @Length INT

  SET @Start = CHARINDEX('<', @HTMLText)

  SET @End = CHARINDEX('>', @HTMLText, CHARINDEX('<',@HTMLText))

  SET @Length = (@End - @Start) + 1

  WHILE @Start > 0

  AND @End > 0

  AND @Length > 0 BEGIN

  SET @HTMLText = STUFF(@HTMLText, @Start, @Length, '')

  SET @Start = CHARINDEX('<', @HTMLText)

  SET @End = CHARINDEX('>', @HTMLText, CHARINDEX('<', @HTMLText))

  SET @Length = (@End - @Start) + 1

  END

  RETURN LTRIM(RTRIM(@HTMLText))

  END

  GO

  Michael L John
  If you assassinate a DBA, would you pull a trigger?
  To properly post on a forum:
  http://www.sqlservercentral.com/articles/61537/