Replace column text from a list of reserved words

Question

I have a table variable which contains a list of words that I want to replace from query results.

I want to search for any of these 600 words in the table variable. I did 3 just for this example. Then, I want to replace the word if it's found. I'm getting results back but it's duplicating per word. I have a UDF which takes each word in the company name and looks to see if it's a match.

 declare @findreservedwords table
(findWord varchar(50) primary key)

 INSERT INTO @findreservedwords
 VALUES
('Inc','LLC','Corp.')
--actually I have over 500 records in the @findreservedwords table variable.  Just 3 for this example 

select distinct p.product_id,replace(c.Company_Name,f.findword,'') as NewCompanyName,f.findWord,sep.col
 FROM PRODUCT p
INNER JOIN COMPANY c on p.Manufacturer_ID = c.company_id
CROSS APPLY dbo.SeparateValues(c.company_name, ' ') sep
LEFT OUTER JOIN @findreservedwords f on f.findWord = sep.col
WHERE p.product_id = 100

This Returns...

Product_ID     NewCompanyName        FindWord      Col

100            null                   null         Sony
100            Sony Inc               LLC          LLC
100            Sony LLC               Inc          Inc

I would like to have it return just one result and both the "LLC" and the "Inc" would be removed since those words are in the reserved words table variable. So the string, "Sony LLC Inc"

would be...

    Product_ID     NewCompanyName  
    100            Sony

Is CLR an option? You could have a regex with the pattern being the words separated by an alternation operator. `(word1|word2)` then replace matches with an empty string. — Martin Smith, Feb 25 '15 at 23:19
Might [this](http://stackoverflow.com/questions/28727375/search-and-replace-a-string-t-sql) help? — HABO, Feb 26 '15 at 04:18

score 1 · Accepted Answer · answered Feb 25 '15 at 23:58

First, simplify your question to just focus on the company name. The join back to product is trivial, but it complicates the query unnecessarily.

Your basic query is:

 select replace(c.Company_Name, f.findword,'') as NewCompanyName,
        f.findWord, sep.col
 FROM COMPANY c CROSS APPLY
      dbo.SeparateValues(c.company_name, ' ') sep LEFT OUTER JOIN   
      @findreservedwords f
      on f.findWord = sep.col;

You could try to recursively do the replace using a recursive CTE. Instead, reconcatenate the name together after removing the words you do not want. I am going to assume that SeparateValues returns an index as well as the word. (You can find split() functions on the web that do this. So, let's reconcancatenate the values together:

select c.Company_Name,
       stuff((select ' ' + sv.findword
              from dbo.SeparateValues(c.company_name) sv left outer join
                   @findreservedwords f
                   on f.findWord = sv.col  
              where f.findword is null
              order by sv.wordnumber
              for xml path ('concat')
             ).Value('/concat[1]', 'varchar(max)'), 1, 1, ''
            ) as NewCompanyName
from company c;

You can use this as a subquery or CTE in other queries to get the result at the product level.

Replace column text from a list of reserved words

1 Answers1