Removing sections from strings in rows returned

[oddball]

Have got a stored procedure that returns all news announcements from a table. The string however contains html references to images eg:


<br><img src=

http://localhost/WAFPortal/images/itemimages/aust104.jpg><br>

Is it possible to remove all content from the string enclosed by < > when returning the results?

cheers,

si

 

[petersJazz]

I dont know the details but try something like this:

WHILE PATINDEX('%<%',@returnstring) > 0
BEGIN
@xstart = PATINDEX('%<%', @returnstring)
@xend = PATINDEX('%>%', @returnstring)
@xlen = LENGTH(@returnstring)
@returnstring = SUBSTRING(@returnstring, 1, @xstart - 1) + SUBSTRING(@returnstring, @xend + 1, @xlen)
END

 

[oddball]

thanks Jazz,

but would this work for only one instance of the image tags, or will it remove all in the string.

cheers,

si

 

[tlbroadbent]

Jazz' solution removes all occurences. Here is another method for your consideration.

While charindex('<',@str)>0
Begin
Set @str = stuff(@str, charindex('<',@str),
charindex('>',@str) - charindex('<',@str) + 1, '')
End

If using SQL 2000, you could create a User-Defined function and use the function inline in a query. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains &quot;Suggestions for Getting Quick and Appropriate Answers&quot; to your questions.

 

[oddball]

thanks guys,

will have to sit down and try to work out what your methods are doing. I had hoped i could use the REPLACE function, like so:


LEFT(CAST(REPLACE(Description , '<br><br><img%><br><br>' , ' ') as nvarchar(4000)), 125) + '...' AS DescriptionShort


As well as removing the html content i'm also croping the result to 125 characters. But it seems the REPLACE function doesn't support wildcards. AAARRRGGGHH!!!


cheers,

si

 

[oddball]

Sorry, not that competent at SQL yet - Help please!

If this is my standard stored procedure, where Description is the column i wish to remove the HTML from:


===========================================================


ALTER PROCEDURE GetAnnouncements
(
@ModuleID int,
)
AS

SELECT
ItemID,
CreatedByUser,
CreatedDate,
Title,
ExpireDate,
Description

FROM
Announcements

WHERE
ModuleID = @ModuleID
AND
ExpireDate > GetDate()

===========================================================


How do i incorporate this code into it:


===========================================================

While charindex('<',@str)>0
Begin
Set @str = stuff(@str, charindex('<',@str),
charindex('>',@str) - charindex('<',@str) + 1, '')
End

===========================================================


I understand now what the loop is doing, just don't know how/where to declare @str, and how to relate it to my returned Description rows.

cheers,

si

 

[oddball]

Help please, am still stuck on how to incorporate While loop into my stored procedure,

cheers,

si

 

[tlbroadbent]

Are you using SQL 2000? If yes then create a UDF as follows. You can create it in the database containing the Announcements table.

Create Function dbo.fnRemoveHTMLString (@str varchar(8000))
Returns varchar(8000)
As

Begin

While charindex('<',@str)>0
Begin
Set @str = stuff(@str, charindex('<',@str),
charindex('>',@str) - charindex('<',@str) + 1, '')
End

Return (@str)

End

Use the UDF in the stored procedure as follows.

ALTER PROCEDURE GetAnnouncements
(@ModuleID int)
AS

SELECT
ItemID,
CreatedByUser,
CreatedDate,
Title,
ExpireDate,
Description=dbo.fnRemoveHTMLString(Description)
FROM Announcements
WHERE ModuleID = @ModuleID
AND ExpireDate > GetDate()

If you have SQL 7 or earlier the stored procedure will be more complex. You will have to loop through the record set to remove the HTML tags. Let me know if you need code for that. Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains &quot;Suggestions for Getting Quick and Appropriate Answers&quot; to your questions.

 

[oddball]

Unfortunatley i'm using SQL 7 (shame that looks a lot easier). Would be grateful for the code for that version,

cheers,

si

 

[tlbroadbent]

Try this.

ALTER PROCEDURE GetAnnouncements
(@ModuleID int)
AS

SELECT
ItemID,
CreatedByUser,
CreatedDate,
Title,
ExpireDate,
Description
INTO #tmptbl
FROM Announcements
WHERE ModuleID = @ModuleID
AND ExpireDate > GetDate()

While Exists
(Select * From #tmptbl
Where charindex('<',Description)>0
And charindex('>',Description)>0)

Begin

Update #tmptbl
Set Description=stuff(Description, charindex('<',Description),
charindex('>',Description) - charindex('<',Description) + 1, '')
Where charindex('<',Description)>0
And charindex('>',Description)>0

End

Select * From #tmptbl

Drop table #tmptbl Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains &quot;Suggestions for Getting Quick and Appropriate Answers&quot; to your questions.

 

[oddball]

Thanks very much Terry, that worked perfectly.

 

[petherin]

These procedures work fine, but what if the string contains a < or a > that isn't an HTML tag. If you had string such as:

<HTML><HEAD><TITLE>This is the title</TITLE><BODY>The number 5 is < the number 6.</BODY></HEAD></HTML>

Then the text you would get would be truncated like this:

This is the titleThe number 5 is

Is there a way to get round this?