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.

Answer 1

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')

Answer 2

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

Answer 3

Easier way:

SELECT 'Fruit' AS Product FROM TABLE

Answer 4

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.

Answer 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

Answer 6

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]

Answer 7

The suggestion to create a "category" table is going to be your best choice for the long run.

For examples check out this link: Data belongs in your tables - not in your code

Answer 8

I'm a novice at SQL, so hopefully someone can spell this out for me. I'm trying to do the same thing as above but with a different table and different fields. Let's say that the following field "ShiptoPlant" in table "BTST" has three records (my table actually has thousands of records)...

ShiptoPlant Plant #1 Plant - 2 Plant/3

Here's what I'm trying to type in the SQL screen:

SELECT CASE WHEN ShipToPlant IN ("#", "-", "/") Then ""
ELSE ShipToPlant END FROM BTST;

I keep getting the message (Error 3075)...

"Syntax error (missing operator) in query expression 'CASE WHEN ShiptoPlant IN (";","/"," ") Then "" ELSE ShipToPlant END'."

I want to do this operation for every character on the keyboard, with exception to "*" since it is a wildcard.

Any help you could provide would be greatly appreciated!

Answer 9

Single quotes to delimit text in SQL:

CASE WHEN ShiptoPlant IN (';','/',' ') Then '' ELSE ShipToPlant END