Strip Those RTF Tags Away

Sep 26, 2007

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"

View 10 Replies


ADVERTISEMENT

SQL Server 2008 :: Strip HTML Tags

Oct 28, 2011

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] .....

View 9 Replies View Related

A Relational Technique To Strip The HTML Tags Out Of A Ntext Datatype Field

Nov 27, 2007

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

View 1 Replies View Related

How To Replace Div Tags With P Tags In A Column

May 6, 2015

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>

View 1 Replies View Related

Stripping .rtf Tags Out

Sep 25, 2007

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 Related

Cleaning Html Tags.

May 5, 2004

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

View 2 Replies View Related

Remove Html Tags From A String!!!

Feb 13, 2008



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

View 9 Replies View Related

Sql Language Help To Generate Tags From Database

Oct 11, 2007

I 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.

View 1 Replies View Related

How To Remove Html Tags From Varchar Value

May 20, 2008

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

View 12 Replies View Related

Join Between Two Tables Where Asset Tags Are Present

Dec 20, 2012

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

View 5 Replies View Related

Remove ROOT Tags From SQL Task XML Results

Dec 19, 2007

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!

View 9 Replies View Related

SQL Server 2012 :: How To Preserve Tags In Varchar Output

May 6, 2015

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)?

View 2 Replies View Related

SQL Server 2008 :: Query To Read From Each Column Of XML Tags?

Jul 31, 2015

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'

View 5 Replies View Related

Exclude Html Tags From Full-text Index?

Oct 18, 2007

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!

View 4 Replies View Related

Strip Certain Characters

Jul 13, 2004

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

View 4 Replies View Related

How Do You Strip Off Stars

Feb 26, 2004

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

View 3 Replies View Related

Full Text Search Indexing HTML - Does The Filter Expect Certain Tags To Be Present As Standard?

Jul 10, 2007

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?!? 

View 1 Replies View Related

Full Text Search Indexing HTML - Does The Filter Expect Certain Tags To Be Present As Standard?

Jul 11, 2007

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?!?

View 1 Replies View Related

Strip String From Csv Column

Mar 26, 2004

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

View 9 Replies View Related

Strip Off Part Of A String

Dec 4, 2000

I am trying to strip off 'XYZ' from column1 in table1 whenever it occurs
Any help appreciated
saad

View 4 Replies View Related

Strip Text From A String

Feb 17, 2004

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.

View 2 Replies View Related

Strip Characters Out Of String

Jan 11, 2002

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

Export To PDF Strip Out Space

Dec 14, 2007

Hi,
I use RS 2005 SP2.
I add a text item value " smth..."
No problem in preview, but export to pdf strip out whitespace characters and then my report in pdf like:
"smth..."

Is there a solution

View 15 Replies View Related

How To Strip A List Of Characters From A Field

Nov 8, 2001

hey,
what the best way of stripping out a list of characters from a specified field in a table. e.g If first name consists of ABCD'E-FSA, we wnat to strip the ' and the -. There is about 15-20 characters like that.
what's the best way of doing it other encapsulating in the replace function that many times.
thanks
zoey

View 2 Replies View Related

Need To Strip Non-numerics From Phone Field

Jul 22, 2004

I tried searching forums for the last hour and could not find much.

Rather then doing...

REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(RE PLACE(ISNULL(PHONE, ''), ' ', ''), '(', ''), ')', ''), '-', ''), '.', ''), ':', ''), '+', '')

I was hoping there was an equally efficient alternative that utilizes PATINDEX('%[^0-9]%', PHONE)

to some capacity (i.e. uses regular expressions to strip all non-numeric characters from the phone field)

I am certain this has been addressed before, just not sure if the "REPLACE to the nth degree" is the only solution.

THANKS!

View 7 Replies View Related

Strip Time Out Of A Date Field - Sql

Dec 3, 2004

Hi all,

I have a table that I've imported into SQL - there is a field in there for date that must have used now(); as its default value (access).

So the values are something like:

01/11/2004 12:16:42

I need a way to change the data so that the time element removed is the field just holds the date. Failing that a way to insert this from the existing field into a new field stripping the date off en route would be a great help.

Many thanks

Phil

View 2 Replies View Related

Strip Off Hour, Minute, Second From A Datetime?

Jan 18, 2005

I would like something I can do inline eg:

select convert(blahdatatype,a.datefield) as smallerdatefield
from
a

where a.datefield is a datetime. If a contains rows like:

datefield
---------------------
01/20/2005 22:17:23
08/23/2001 03:04:15
...

Then the SQL above returns:

smallerdatefield
---------------------
01/20/2005 00:00:00
08/23/2001 00:00:00
...

Is there any non-obnoxious way (eg: without have to result to using datepart a million times) to do this? For instance, Oracle provides a function called Trunc which does it, but I cannot find an SQL Server equivalent. Anyone? TIA!!!

View 9 Replies View Related

Strip Out The From Alphabetized Search Results?

Jan 16, 2006

I am not aware of this if it exists in MS SQL server. But I need to return results in alphabetic order ignoring "The" if it's the first word of a title...

so for example title "The Cliffhanger" would be returned along with other titles that start with letter C, but "The" also must be returned as part of the title, but just ignored while alphabetizing.

I'm sure that I'm not the first one to ever need this and don't want to re-invent the wheel, so if you have any ideas as to the best way to accomplish this, help me out.

Thanks in advance.

View 8 Replies View Related

Strip Data From One Column And Create Another

Mar 5, 2006

Hi All.

I have a coulmn that contains data in the following format.

COUNTRY/SATE/UNAME

What I would like to do is create another column in the table stripping out the COUNTRY/ and /UNAME

Example
Existing column value
US/NY/BLAH

New Column value
NY

View 3 Replies View Related

Strip Time From Date String

Apr 23, 2008

stupid question - how do I strip the time portion from a date returned from now() or

Globals!ExecutionTime.

I MS either used consistent date functions across all platforms or include help in BOL for their VB functions in SSRS.....

View 5 Replies View Related

How Do I Strip The Time Portion From The Getdate() Value?

Sep 1, 2006

the ssis expression language getdate() function returns the current date with the current time. i only need to get the current date, without the current time. for example: 9/1/2006

how would i construct the proper expression to return this value?

thanks in advance.

View 2 Replies View Related

Strip HTML Encoding Out Of A String In Sql Clr

Apr 3, 2008

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 Related

Elegant Way To Strip Off Date From Datetime?

Dec 24, 2007

if all I want is 12am of the date provided in a datetime column, is there a more elegant way of doing this than something like...


convert (datetime,

convert(varchar(2), month([time])) + '/' +

convert(varchar(2), day([time])) + '/' +

convert(varchar(4), year([time]))

)

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved