Replacing all occurrences of text in table 1 with text from table 2 iteratively

Question

Replacing all occurrences of text in table 1 with text from table 2 iteratively

kp81

SSC Veteran

Points: 225
More actions
March 10, 2011 at 3:56 am

#233098

Hi all,
Ok, so I've been searching for last couple of days for a solution for this, but despite my best efforts I can't find one. Basically my problem is this:
I have a table (MESSAGES) that has a [message VARCHAR(1000)] column in it. I have a second table (PROFANITIES) that has two columns [Profanity VARCHAR(10)] and [Replacement VARCHAR(10)] in it. My idea is to replace all occurrences of PROFANITIES.Profanity in MESSAGES.message with PROFANITIES.Replacement.
I've seen there are CLR solutions out there, however in the environment I'm working in, they're unsupported. I'm using SQL Server 2005, and would prefer not to use CURSORS as the MESSAGES table has the potential to become HUGE!
Edit: Also, I don't want to update the MESSAGES table as I want to keep the original text intact, so the functionality will be within a SELECT.
If anyone has a quick way of doing this, I will be enternally grateful!
Many thanks in advance guys,
Kev.

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323546 More actions · Answer 1

i would simplify the issue a bit:

items that contain badwords get archived off to a separate audit table, then update the messages directly to remove the bad words; the overhead on updating a varchar 1000 with multiple replaces(20 or so replaces?) would slow down the application(a forum?) too much to do it dynamically, i would think.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

kp81 SSC Veteran Points: 225 More actions · Answer 2

Lowell (3/10/2011)
i would simplify the issue a bit:
items that contain badwords get archived off to a separate audit table, then update the messages directly to remove the bad words; the overhead on updating a varchar 1000 with multiple replaces(20 or so replaces?) would slow down the application(a forum?) too much to do it dynamically, i would think.

Hi Lowell,

That's a great idea. I did want users to be able to be able to toggle whether or not profanities are displayed or not, hence the need for "dynamicism". Using your idea, upon inserting the message into the db, I might surround all the profanities (and their replacements) with <span> tags and do some funky CSS when it's rendered on the web page depending on if the user has toggled to display profanities or not.

Thanks for the "thinking-outside-the-box" idea, getting the data right from the start means not having to spend ages trying to fix it later!

Cheers again,

Kev.

Lowell SSC Guru Points: 323546 More actions · Answer 3

your idea is even easier to implement; you simply update the source with some tags and poof, some simple CSS on the page and they can see or not see the bad words;

i can't take any credit for anything, other than , like you said, helping you see outside the box. there's always more than one way to accomplish this stuff.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!