Solved

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

Posted on 2008-10-05
7
682 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 59

Assisted Solution

by:Kevin Cross
Kevin Cross earned 300 total points
Comment Utility
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 59

Accepted Solution

by:
Kevin Cross earned 300 total points
Comment Utility
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 200 total points
Comment Utility
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 59

Expert Comment

by:Kevin Cross
Comment Utility
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 59

Expert Comment

by:Kevin Cross
Comment Utility
0
 
LVL 5

Author Comment

by:Lawrence Barnes
Comment Utility
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
Comment Utility
I am just returning home (late this evening) and will get to back this anon.  
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
CCModeler offers a way to enter basic information like entities, attributes and relationships and export them as yEd or erviz diagram. It also can import existing Access or SQL Server tables with relationships.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

762 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now