I have a table with a column that has html text. The column with html text is pretty big datatye varchar(max)... I wanted to check if any of you have any function that I can use to Strip out the HTML tags... I saw couple of version online, but it was running too slow..
This is the one I used: [URL] .....
I had a problem with the ntext datatype. I need to strip the HTML tags out of a ntext datatype column. I have sample query for that, which works fine for STRING, as stuff is the string function, what to do for ntext field.
=======The Process follows like this =========
--**************************************
--
-- Name: A relational technique to strip
-- the HTML tags out of a string
-- Description:A relational technique to
-- strip the HTML tags out of a string. Th
-- is solution demonstrates how to use simp
-- le tables & search functions effectively
-- in SQL Server to solve procedural / ite
-- rative problems.
-- This table contains the tags to be re
-- placed. The % in <head%>
-- will take care of any extra informati
-- on in the tag that you needn't worry
-- about as a whole. In any case, this t
-- able contains all the tags that needs
-- to be search & replaced.
CREATE TABLE #html ( tag varchar(30) )
INSERT #html VALUES ( '<html>' )
INSERT #html VALUES ( '<head%>' )
INSERT #html VALUES ( '<title%>' )
INSERT #html VALUES ( '<link%>' )
INSERT #html VALUES ( '</title>' )
INSERT #html VALUES ( '</head>' )
INSERT #html VALUES ( '<body%>' )
INSERT #html VALUES ( '</html>' )
go
-- A simple table with the HTML strings
CREATE TABLE #t ( id tinyint IDENTITY , string varchar(255) )
INSERT #t VALUES (
'<HTML><HEAD><TITLE>Some Name</TITLE>
<LINK REL="stylesheet" HREF="/style.css" TYPE="text/css" ></HEAD>
<BODY BGCOLOR="FFFFFF" VLINK="#444444">
SOME HTML text after the body</HTML>'
)
INSERT #t VALUES (
'<HTML><HEAD><TITLE>Another Name</TITLE>
<LINK REL="stylesheet" HREF="/style.css"></HEAD>
<BODY BGCOLOR="FFFFFF" VLINK="#444444">Another HTML text after the body</HTML>'
)
go
-- This is the code to strip the tags out.
-- It finds the starting location of eac
-- h tag in the HTML string ,
-- finds the length of the tag with the
-- extra properties if any. This is
-- done by locating the end of the tag n
-- amely '>'. The same is done
-- in a loop till all tags are replaced.
BEGIN TRAN
WHILE exists(select * FROM #t JOIN #html on patindex('%' + tag + '%' , string ) > 0 )
UPDATE #t
SET string = stuff( string , patindex('%' + tag + '%' , string ) ,
charindex( '>' , string , patindex('%' + tag + '%' , string ) )
- patindex('%' + tag + '%' , string ) + 1 , '' )
FROM #t JOIN #html
ON patindex('%' + tag + '%' , string ) > 0
SELECT * FROM #t
rollback
This algorithm can be used to strip out HTML tags too.
With reference to http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=89973
and http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=90000CREATE FUNCTIONdbo.fnParseRTF
(
@rtf VARCHAR(8000)
)
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE@Stage TABLE
(
Chr CHAR(1),
Pos INT
)
INSERT@Stage
(
Chr,
Pos
)
SELECTSUBSTRING(@rtf, Number, 1),
Number
FROMmaster..spt_values
WHEREType = 'p'
AND SUBSTRING(@rtf, Number, 1) IN ('{', '}')
DECLARE@Pos1 INT,
@Pos2 INT
SELECT@Pos1 = MIN(Pos),
@Pos2 = MAX(Pos)
FROM@Stage
DELETE
FROM@Stage
WHEREPos IN (@Pos1, @Pos2)
WHILE 1 = 1
BEGIN
SELECT TOP 1@Pos1 = s1.Pos,
@Pos2 = s2.Pos
FROM@Stage AS s1
INNER JOIN@Stage AS s2 ON s2.Pos > s1.Pos
WHEREs1.Chr = '{'
AND s2.Chr = '}'
ORDER BYs2.Pos - s1.Pos
IF @@ROWCOUNT = 0
BREAK
DELETE
FROM@Stage
WHEREPos IN (@Pos1, @Pos2)
UPDATE@Stage
SETPos = Pos - @Pos2 + @Pos1 - 1
WHEREPos > @Pos2
SET @rtf = STUFF(@rtf, @Pos1, @Pos2 - @Pos1 + 1, '')
END
SET@Pos1 = PATINDEX('%cf[0123456789][0123456789 ]%', @rtf)
WHILE @Pos1 > 0
SELECT@Pos2 = CHARINDEX(' ', @rtf, @Pos1 + 1),
@rtf = STUFF(@rtf, @Pos1, @Pos2 - @Pos1 + 1, ''),
@Pos1 = PATINDEX('%cf[0123456789][0123456789 ]%', @rtf)
SELECT@rtf = REPLACE(@rtf, 'pard', ''),
@rtf = REPLACE(@rtf, 'par', ''),
@rtf = LEFT(@rtf, LEN(@rtf) - 1)
SELECT@rtf = REPLACE(@rtf, '0 ', ''),
@rtf = REPLACE(@rtf, ' ', '')
SELECT@rtf = STUFF(@rtf, 1, CHARINDEX(' ', @rtf), '')
RETURN@rtf
ENDE 12°55'05.25"
N 56°04'39.16"
does any one has any sql server function that passes some text and returns a string without html tags.
example:
nice day
should return nice day
or if other html tags strip them off.
thanks for your help.
-Fr
I have a column of string which has html tags attached to it. How can I remove them..other than manually going and doing it? Any funtions?
Thanks!!
Tanya
Hi !
i have a function written in c# which removes all html tags from the provide string like
public static string RemoveHTML(string HTML)
{
return Regex.Replace(HTML, "<(.|)*?>", "");
}
how can i apply such functionality to varchar field which removes all the html tags from it in stored procedure
Regards,
DiL
I ran a CONTAINS query for the word "target" in a bunch of index web pages. I came up with lots of matches -- but they were all inside html tags:
<a href="www.foo.com" target = "_blank">lorem ipsum</a>
Is there a good way to exclude tags (and their attributes) from the full-text index?
Thanks!
The below query will read the data in XML format but any query to read from each column of XML tags easily?
SELECT CAST(record AS XML), record
FROM sys.dm_os_ring_buffers
WHERE ring_buffer_type = 'RING_BUFFER_CONNECTIVITY'
I am trying to do string scrubbing in a sql clr function, including removing certain HTML formatting. I would like to use HtmlDecode method, but it's my understanding that System.Web is not available for Sql Clr (without marking code unsafe - not an option for me as this is for an application we sell externally, and unsafe calls woudl not go over well with customers). Is there any class that IS supported for Sql Clr that exposes this functionality? Thanks.
View 10 Replies View RelatedHi, I was wondering if any SQL Server gurus out there could help me...I
have a table which contains text resources for my application. The text
resources are multi-lingual so I've read that if I add a html language
indicator meta tag e.g.<META NAME="MS.LOCALE" CONTENT="ES">and
store the text in a varbinary column with a supporting Document Type
column containing ".html" of varchar(5) then the full text index
service should be intelligent about the language word breakers it
applies when indexing the text. (I hope this is correct technique for
best multi-lingual support in a single table?)However, when I come to query this data the results always return 0 rows (no errors are encountered). e.g.DECLARE @SearchWord nvarchar(256)SET @SearchWord = 'search' -- Yes, this word is definitely present in my resources.SELECT * FROM Resource WHERE CONTAINS(Document, @SearchWord)I'm a little puzzled as Full Text search is working fine on another table that employs an nvarchar column (just plain text, no html).Does the filter used for full text indexing of html expect certain tags to be present as standard? E.g. <html> and <body> tags? At present the data I have stored might look like this (no html or body wrapping tags):Example record 1 data: <META NAME="MS.LOCALE" CONTENT="EN">Search for keywords:Example record 2 data: <META NAME="MS.LOCALE" CONTENT="EN">Sorry no results were found for your search.etc.Any pointers / suggestions would be greatly appreciated. Cheers,Gavin.UPDATE: I have tried wrapping the text in more usual html tags and re-built the full text index but I still never get any rows returned for my query results. Example of content wrapping tried - <HTML><HEAD><META NAME="MS.LOCALE" CONTENT="EN"></HEAD><BODY>Test text.</BODY></HTML>I've also tried stripping all html tags from the content and set the Document Type column = .txt but I still get no rows returned?!?
Hi, I was wondering if any SQL Server gurus out there could help me...
I have a table which contains text resources for my application. The text resources are multi-lingual so I've read that if I add a html language indicator meta tag e.g.
<META NAME="MS.LOCALE" CONTENT="ES">
and store the text in a varbinary column with a supporting Document Type column containing ".html" of varchar(5) then the full text index service should be intelligent about the language word breakers it applies when indexing the text. (I hope this is correct technique for best multi-lingual support in a single table?)
However, when I come to query this data the results always return 0 rows (no errors are encountered). e.g.
DECLARE @SearchWord nvarchar(256)
SET @SearchWord = 'search' -- Yes, this word is definitely present in my resources.
SELECT * FROM Resource WHERE CONTAINS(Document, @SearchWord)
I'm a little puzzled as Full Text search is working fine on another table that employs an nvarchar column (just plain text, no html).
Does the filter used for full text indexing of html expect certain tags to be present as standard? E.g. <html> and <body> tags? At present the data I have stored might look like this (no html or body wrapping tags):
Example record 1 data: <META NAME="MS.LOCALE" CONTENT="EN">Search for keywords:
Example record 2 data: <META NAME="MS.LOCALE" CONTENT="EN">Sorry no results were found for your search.
etc.
Any pointers / suggestions would be greatly appreciated. Cheers,
Gavin.
UPDATE: I have tried wrapping the text in more usual html tags and re-built the full text index but I still never get any rows returned for my query results. Example of content wrapping tried - <HTML><HEAD><META NAME="MS.LOCALE" CONTENT="EN"></HEAD><BODY>Test text.</BODY></HTML>
I've also tried stripping all html tags from the content and set the Document Type column = .txt but I still get no rows returned?!?
When sending an email in HTML format, shouldn't this allow for 2gb of data? Mine is getting truncated after 4000 characters.
@body NVARCHAR(MAX) = NULL,
EXEC msdb.dbo.sp_send_dbmail
@recipients='someone@some.com',
@reply_to='someone'
@from_address='someone@here.com>',
@profile_name = 'profilename',
@body_format = 'HTML',
@body = 'lots of data'
I want to replace div tags with p tags in a column in sql.
<div style: bold> abc </abc>
<div> efgh></div>
required output:
<p>abc</p>
<p>efgh</p>
I have following XQuery:
declare @xmldoc as xml
select @xmldoc = '<Text>This is firstline<Break />This is second line<Break />This is third line</Text>'
select @xmldoc.value('(/Text)[1]','varchar(max)')Result is: "This is firstlineThis is second lineThis is third line"
My problem is, that the <Break /> tags within the text are removed in the conversion to varchar. How to preserve the such tags in the varchar output? Or to get the <Break /> tags "translated" to e.g. CHAR(10)?
I am looking for the fastest way to strip non-numeric characters from a string.
I have a user database that has a column (USER_TELNO) in which the user can drop a telephone number (for example '+31 (0)12-123 456'). An extra computed column (FORMATTED_TELNO) should contain the formatted telephone number (31012123456 in the example)
Note: the column FORMATTED_TELNO must be indexed, so the UDF in the computed column has WITH SCHEMABINDING.... I think this implicates that a CLR call won't work....
I am retrieving a field from SQL and displaying that data on a web page.
The data contains a mixture of text and html codes, like this "<b>test</b>".
But rather than displaying the word test in bold, it is displaying the entire sting as text.
How do I get it to treat the HTML as HTML?
Does anyone know how to get rid of rtf tags that are stored in the table? I need to filter out the data and wondering if there is a utility on the SQL Server that can do it.
View 16 Replies View RelatedI have generated a database for my website, I intend on using software that will convert the database into static web pages.
Big problem I have I am not a programmer, but I know a tiny bit about tags etc. for search engines.
The meta tag description is what I want to create using a field in this database.
The software I am about to use has a sql builder is there anyway it could be done be highlighting the relavent field and using sql language.
PLEASE someone Help
This problem has been driving me around the twist.
There are two tables A and B where asset tags are present, but in one table in rows and in another in column wise.
for eg
ASSet Tag
SR-062009-00032966
SR-062009-00032962
SR-072009-00020572
SR-072009-00020571
SR-072009-00020585
HH-092009-00038342
Table B
field 1 -->Asset TAG
Record 1-->SR-072009-00020572,SR-072009-00020571,SR-062009-00020685,SR-072009-00001592,SR-072009-00001376,SR-062009-00020683,SR-092009-00001617
field 2 --> Material code
REcord 1-->121
REcord 2-->123
What is the query so that asset tag of A matches with each and every asset tag table of B and output comes as
Output
Asset TAg -------- MAterial Code
SR-062009-00032966
SR-062009-00032962
SR-072009-00020572 ------121
SR-072009-00020571 -------121
SR-072009-00020585
Hi All,
I'm currently using an Execute SQL Task to return XML data from a query into an SSIS string variable. In my FOR XML clause in SQL I'm specifying a certain name for my root tag, called "Accounts". This works great in Management Studio, however, the Execute SQL Task appends a <ROOT> and </ROOT> tag to the start and end of the string, so now it looks like:
<ROOT><Accounts>...all my elements...</Accounts></ROOT>
I'd like to remove the ROOT tags so that the <Accounts> tags are actually the root for this doc. What would be the best way to remove the ROOT tags from the SSIS string variable?
Thanks!
Hi y'all I want my written text in an textarea store in a sql database. Which format do i have to choose so sql server understands <br>. Now i use ntext and i get a scrambled <br> (2 cubes)Thanks in advance.Grtz
View 4 Replies View RelatedI am using a Wysiwig editor, FCKeditor, i my CMS. I try to save the html text from the editor in a SQL Server. But noting get stored in the database.
I think the problem is how the tabel in the datebase is setup.
How shall a tabel look like so it can store html?
There are a few features in the new SQL Server - Reporting Services that I really need in production. I have tested everything and it works great. I am running the CTP version since Microsoft is saying they aren't releasing the release version until 3rd quarter 2008.
Since Microsoft won't sell SQL 2008 until 3rd quarter, can I run the CTP in production until the release and then purchase SQL 2008?
Jim
Can anyone tell me how I can strip certain chahrcters from a string
I know I can use replace, but i don't think this is appropriate for what I want to do
For example I have the string
declare @text varchar (100)
select @text = 'word1, word2 & word3'
if i do a replace on the string like this
select @text = 'replace(@text, ',', '')
select @text = 'replace(@text, '&', '')
I end up with the string
select @text = 'word1 word2 word3'
i.e. 2 spaces between word2 and word3
What i want the string to look like is :
select @text = 'word1 word2 word3'
Is there a way i can check for more than one space + characters ( / , &) in one go
many thanks
I don't know how the stars are attribuated
but I don't think I should have so many
It gives a false impression to new members
I need to store some HTML, well over 8000 chars in SQL Server, what would be the best data type to use?
Thanks
Simon
I have been coding in php for a while now and Im starting to get into asp.net. I use htmlarea in php as my html editor, but I was told that it shouldnt be used when using asp.net and sql server, because of it opens the server up for attack scripts. I dont understand why. Can someone help me out? I would really like to use a html editor with my applications but if it opens the server up for attacks then there is no point on using it.Thanks,Chad
View 1 Replies View Related
Hello - does anyone have experience w/SQL Server 2005 in a virtual environment? I'm considering this for a production environment but not sure if performance will suffer. Our databases will have a lot of writing but not too much reading. A SSRS solution is currently the only app. connecting to the SQL db. Max users to server at any given time will be very low (~10 users max). But the databases are pulling in data from other, outside multiple data sources on a daily basis.
Any pointers to documentation or any advice?
Thanks,
A Brown
Hello!
Recently, I set up server with Windows Web Server 2008 RC1, SQL 2008 Express beta, .NET 3.5, IIS 7.
I'm running ASP.NET web application with SQL database. Everything works fine until the first application state on the server expires. After that, any postback that starts a new application state on the server and connects to the database, results in the following error:
Failed to generate a user instance of SQL Server due to a failure in starting the process for the user instance. The connection will be closed.
Is this a bug that will be fixed in release of Windows / SQL or am I doing something wrong?
Many thanks for help,
Jan
I have a column of 5 comma-separated-value strings:
stringA, stringB, stringC, stringD, stringE
The strings are GUID's with the hyphen stripped and made all uppercase so they are completely random. I need to be able to remove any one of the strings including the comma, in a stored procedure and I am not sure how to accomplish this.
SELECT tickets
FROM users
WHERE CONTAINS (tickets, @ticket)
IF @@rowcount > 0
REMOVE STUFF HERE
SET @valid = 1
ELSE
SET @valid = 0
So if stringB gets passed in as @ticket then the new value in the column would be :
stringA, stringC, stringD, stringE
Any help is greatly appreciated.
Thank you
dave
I am trying to strip off 'XYZ' from column1 in table1 whenever it occurs
Any help appreciated
saad
I'm trying to find a way to strip text from a string. In the past (pre SQL Server) I would've used
LName: Left(NCBH!Name,InStr(1,NCBH!NAME,",",1)-1)
To strip the last name from a string like
Franks,George J
Apparently InStr is not a recognized function in SQL Server 2000. Or is it available but not in a view?
Any thoughts would be greatly appreciated.
I have a phone number string (416) 555-5555 in a table. I'd like to perform a search on the string so that the user is able to pass any number, and the query returns all phone numbers like it. What I'd like to do is to strip out the brackets and dashes and perform a like search.
View 4 Replies View Related