Complex Find and Replace in SQL

Question

I have a list of ProductName key/value pairs, like so:

CREATE TABLE [dbo].[ProductNames]
(
    [Key] [nvarchar](100) NULL,
    [Value] [nvarchar](100) NULL
)

Containing values like this:

'Pepsi', 'Pepsi®'
'Coke', 'Coke®'

I need to find and replace values in a text field that DO NOT ALREADY contain the registered trademark for a product...and replace it with the full trademark string.

FOR EXAMPLE:

 1. if 'Pepsi' is alone....it becomes 'Pepsi®'
 2. if 'Pepsi®' already exists...do nothing

UPDATE: The registered trademark is only one example of something to be replaced. There could be other multi-character replacements as well. As such, something more complex is probably needed. For example, I would probably detect 'Pepsi', then truncate 'Pepsi' from the VALUE portion of the key/value row...AND THEN...try to detect if that truncated value already exists in the string. If not, then go ahead and replace the value (something like that).

Adriano Carneiro · Accepted Answer · 2012-08-02 14:35:39Z

2

Perhaps this solution will help you. It will update records where value does not end with ®, appending it at the end.

update ProductNames
set Value = Value + '®'
where Value not like '%®'

answered Aug 2, 2012 at 14:35

Adriano Carneiro

58.8k12 gold badges94 silver badges123 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Prisoner ZERO Over a year ago

Thank you for the reply. However, the registered trademark is only a single example of what is in the source table.

AnandPhadke · Accepted Answer · 2012-08-02 15:08:04Z

0

update ProductNames set 
   Value = CASE WHEN CHARINDEX('®',value,1) = 0 then value+'®' else value end

answered Aug 2, 2012 at 15:08

AnandPhadke

13.5k5 gold badges28 silver badges34 bronze badges

Comments

JLi · Accepted Answer · 2012-08-03 06:51:46Z

0

I can't think of an easy way for this to work. Iterate through each special keyword and check the beginning, middle and end cases for these words, assuming you require white space to separate each keyword. Break down the operation into a few steps, as required by business logic. Expensive yes, but it's a one time thing, I assume. Better yet, run this routine once when a row is added/modified.

edited Aug 3, 2012 at 6:51

answered Aug 3, 2012 at 6:46

JLi

413 bronze badges

Collectives™ on Stack Overflow

Complex Find and Replace in SQL

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related