Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Replace Multiple Strings in SQL Query

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.

like image 832
Eric Ness Avatar asked Feb 02 '09 15:02

Eric Ness


People also ask

How do I replace multiple values in a string in SQL?

If you wanted to replace the words with blank string, go with REGEXP_REPLACE() . If you want to replace the words with other words, for example replacing & with and then use replace() . If there are multiple words to be replaced, use multiple nested replace() .

How replace all occurrences of a string in SQL?

SQL Server REPLACE() Function The REPLACE() function replaces all occurrences of a substring within a string, with a new substring. Note: The search is case-insensitive.


2 Answers

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]
like image 168
Joel Coehoorn Avatar answered Nov 15 '22 18:11

Joel Coehoorn


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.

like image 20
BradC Avatar answered Nov 15 '22 17:11

BradC