?
Solved

SQL 2000 Replace - How to find/repllace multiple values using a table

Posted on 2008-10-05
7
Medium Priority
?
698 Views
Last Modified: 2013-11-15
I'm using SQL 2000.  I have a text field (ResumeRaw) in a table where resume data is posted and another field (ResumeClean) where I want the adjusted resume data to appear.  Some of the resume data has html tags which must be removed.  I have another table, with an ID field, containing the specific tags (BadTags) that I want removed.  To use the replace function to find each of the tags I want removed, I'm using the loop below.  (I know it's something simple, but it escapes me.)  The code below runs, the rows are adjusted, and values are placed in the ResumeClean field, but the html tags are not stripped out.  What am I missing?
DECLARE @TagMin INT
DECLARE @TagMax INT
DECLARE @TagCounter INT
DECLARE @CurrentTag VARCHAR(100)
SET @TagMin = (SELECT MIN([TagID]) FROM [TagsOK])
SET @TagMax = (SELECT MAX([TagID]) FROM [TagsOK])
SET @TagCounter = @TagMin
 
WHILE @TagCounter <= @TagMax
	BEGIN
SET @CurrentTag = (SELECT [BadTag] FROM [Tags] WHERE [TagID] = @TagCounter)
 
UPDATE [ResumeData]
SET [ResumeClean] = REPLACE([ResumeRaw],@CurrentTag,'')
SET @TagCounter = @TagCounter + 1
END

Open in new window

0
Comment
Question by:Lawrence Barnes
  • 4
  • 2
7 Comments
 
LVL 61

Assisted Solution

by:Kevin Cross
Kevin Cross earned 900 total points
ID: 22645060
Firstly, I would change this:
SET @CurrentTag = (SELECT [BadTag] FROM [Tags] WHERE [TagID] = @TagCounter)

To:
SELECT @CurrentTag = [BadTag] FROM [Tags] WHERE [TagID] = @TagCounter

Then make sure that the [BadTag] value returns the tagname correctly and then I would account for end tags as well.

REPLACE(REPLACE([ResumeRaw], '<' + @CurrentTag + '>', ''), '</' + @CurrentTag + '>', '')

One thing that you will have to check for in your data as well is that tags appear as just <tagname>.  If they appear as <tagname > OR <tagname attribute="value" attribute2="value2"> then your match will not work and thus you get tag still in resulting text.

Hope that helps.

Regards,
kevin
0
 
LVL 61

Accepted Solution

by:
Kevin Cross earned 900 total points
ID: 22645072
This question was for stripping all HTML tags, but you can use the ideas in the stored procedure/functions shown to get an idea of what you may have to do if the parsing gets a little more complicated because of the factors I stated above.

http:Q_23755659.html
0
 
LVL 42

Assisted Solution

by:dqmq
dqmq earned 600 total points
ID: 22645129
Possibly, some of the problem is that you use the [TagsOK] table to establish the upper limit of your loop, but then you loop thru the [Tags] table.

Also, that loop is a sloppy way to code because it depends, unnecessarily on sequential ID's in the [Tags] table.  This is one case where a cursor would simplify your coding signficantly.



 

 
DECLARE @CurrentTag VARCHAR(100)
DECLARE BadTags CURSOR FOR SELECT [BadTag] FROM [Tags]
 
OPEN BadTags
FETCH NEXT FROM BadTags INTO @CurrentTag
WHILE @@FETCH_STATUS = 0
  BEGIN
  UPDATE [ResumeData]
    SET [ResumeClean] = REPLACE([ResumeRaw],@CurrentTag,'')
  FETCH NEXT FROM BadTags INTO @CurrentTag
  END
CLOSE BadTags
DEALLOCATE BadTags

Open in new window

0
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

 
LVL 61

Expert Comment

by:Kevin Cross
ID: 22645154
Good point, dgmg.

<off-topic>Dgmg, since I know you are one of the Access officianados, you may want to look at helping this user http:Q_23788587.html.</off-topic>
0
 
LVL 61

Expert Comment

by:Kevin Cross
ID: 22645158
0
 
LVL 5

Author Comment

by:Lawrence Barnes
ID: 22645198
Wow, thank you for the quick responses.  I'll start trying the different approaches.  I'm new, so a bit slow...it will be today though.  There's a similar, but more elaboroate question posted as well... if you want to expand on a similar topic.

dgmg: I'll check the table references but I think its a typo.  After I copied/pasted the code I simplified the table/field names.  Thank you for your loop example!  This was my very first loop and I'm glad to upgrade.
0
 
LVL 5

Author Comment

by:Lawrence Barnes
ID: 22647078
I am just returning home (late this evening) and will get to back this anon.  
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Among the most obnoxious of Exchange errors is error 1216 – Attached Database Mismatch error of the Jet Database Engine. When faced with this error, users may have to suffer from mailbox inaccessibility and in worst situations, permanent data loss.
This article will show a step by step guide on how to mask column values in Oracle 12c using DBMS_REDACT full redaction option. This option is available on licensed Oracle Enterprise edition as part of Oracle's Advanced Security.
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
Planning to migrate your EDB file(s) to a new or an existing Outlook PST file? This video will guide you how to convert EDB file(s) to PST. Besides this, it also describes, how one can easily search any item(s) from multiple folders or mailboxes…

569 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question