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.
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() .
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.
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]
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With