Replace Multiple Strings in SQL Query

Question

I'm writing a SQL query in SQL Server in which I need to replace multiple string values with a single string value. For example

Product     Quantity
-------     --------
Apple       2
Orange      3
Banana      1
Vegetable   7
Dairy       6

would become

Product     Quantity
-------     --------
Fruit       2
Fruit       3
Fruit       1
Vegetable   7
Dairy       6

The only way I know how to do this is to use a nested REPLACE in the SELECT clause.

SELECT
  REPLACE('Banana', REPLACE('Orange', REPLACE('Banana', Product, 'Fruit'),
           'Fruit'), 'Fruit') AS Product
FROM
  Table

Is there an easier way?

EDIT: There may be other values in the Product category. See edited example above.

Solution 1

BradC has the best answer so far, but in case you are for some reason unable to create the additional table I wanted to post an adaption of Kibbee's answer:

SELECT
    CASE WHEN Product IN ('Banana', 'Apple', 'Orange') Then 'Fruit'
    ELSE Product END 
FROM [Table]

Solution 2

Make a new "category" table that has a list of your products, along with the "category" to which they belong.

Then just do an inner join.

Solution 3

Select
Case Product WHEN 'Banana' Then 'Fruit'
WHEN 'Apple' Then 'Fruit'
WHEN 'Orange' Then 'Fruit'
ELSE Product
END
FROM Table

Solution 4

If there are no other products than those mentioned you could do:

SELECT 'Fruit' AS Product,
        Quantity
FROM Table

If there are other products jus add a WHERE clause

WHERE Product IN ('Banana', 'Orange', 'Apple')

Solution 5

You could create a temp table with a single column 'Product' column, and insert all the product names you want to replace.

Then do an inner join against the target table for your update.

UPDATE
    Table
SET Product = 'Fruit'
FROM
    Table t1 INNER JOIN #Table t2 on t1.Product = t2.Product

View more solutions

Comments

[Eric Ness]

This will work the best for my purposes since the database I'm pulling from is an archive. Otherwise adding a category column would be the way to go. Thanks!

[Eric Ness]

This would be a good solution except that I'm pulling from a read-only archive.

[Joel Coehoorn]

He's talking adding a little lookup table, and that's pretty easy to do, even for an archive db.