-- Courtesy of MSSQLTips.com

-- Script to convert data
SET NOCOUNT ON 

DECLARE @word VARCHAR(50),  
       @position INT,  
       @newProductName VARCHAR(500),  
       @oldProductName VARCHAR(500),  
       @newWord VARCHAR(50), 
       @ProductName VARCHAR(500) 

DECLARE load_cursor CURSOR FOR 
       SELECT ProductName 
       FROM Products 

OPEN load_cursor 
FETCH NEXT FROM load_cursor INTO @ProductName 

WHILE @@FETCH_STATUS = 0 
BEGIN 
       SET @oldProductName = @ProductName 
       SET @ProductName = LTRIM(RTRIM(@ProductName)) 
        
       SET @newProductName = @ProductName 

       SET @position = CHARINDEX(' ', @ProductName, 1) 

       BEGIN 
               WHILE @position > 0 
               BEGIN 
                       SET @word = LTRIM(RTRIM(LEFT(@ProductName, @position - 1))) 
                       IF @word <> '' 
                       BEGIN 
                                
                               SELECT @newWord = NULL 
                               SELECT @newWord = synonym FROM Synonyms WHERE word = @word  
                               IF @newWord IS NOT NULL 
                               BEGIN 
                                       SET @newProductName = REPLACE(@newProductName, @word, @newWord) 
                               END 
                       END 
                       SET @ProductName = RIGHT(@ProductName, LEN(@ProductName) - @position) 
                       SET @position = CHARINDEX(' ', @ProductName, 1) 
               END 
                
               SET @word = @ProductName 
               SELECT @newWord = NULL 
               SELECT @newWord = synonym FROM Synonyms WHERE word = @word 
               IF @newWord IS NOT NULL 
                       SET @newProductName = REPLACE(@newProductName, @ProductName, @newWord) 
       END 

       IF @oldProductName <> @newProductName 
       BEGIN 
               SELECT @oldProductName AS OldProductName, @newProductName AS NewProductName 
       END 

       FETCH NEXT FROM load_cursor INTO @ProductName 
END 

CLOSE load_cursor 
DEALLOCATE load_cursor 
GO 



-- Create Table
CREATE TABLE [dbo].[Synonyms] ( 
       [synonym] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , 
       [word] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL  
) ON [PRIMARY] 
GO 

 CREATE  UNIQUE  INDEX [IX_word] ON [dbo].[Synonyms]([word]) ON [PRIMARY] 
GO 


-- Load Sample Data
INSERT INTO synonyms VALUES ('Jam','Marmalade') 
INSERT INTO synonyms VALUES ('Chowda','Chowder') 
INSERT INTO synonyms VALUES ('Wicked Hot','Hot') 
INSERT INTO synonyms VALUES ('King','Sir')