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.

Joel Coehoorn · Accepted Answer · 2009-02-02 16:16:13Z

20

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]

answered Feb 2, 2009 at 16:16

Joel Coehoorn

418k114 gold badges582 silver badges820 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Eric Ness Over a year ago

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!

Joel Coehoorn Over a year ago

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

BradC · Accepted Answer · 2009-02-02 15:55:18Z

11

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.

answered Feb 2, 2009 at 15:55

BradC

40.3k14 gold badges77 silver badges91 bronze badges

1 Comment

Eric Ness Over a year ago

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

Kibbee · Accepted Answer · 2009-02-02 15:53:59Z

7

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

answered Feb 2, 2009 at 15:53

Kibbee

66.2k28 gold badges145 silver badges184 bronze badges

Comments

Rockcoder · Accepted Answer · 2009-02-02 15:53:08Z

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

answered Feb 2, 2009 at 15:53

Rockcoder

8,4993 gold badges35 silver badges41 bronze badges

Comments

Adam Ralph · Accepted Answer · 2009-02-02 15:57:17Z

1

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

answered Feb 2, 2009 at 15:57

Adam Ralph

30k5 gold badges62 silver badges68 bronze badges

Comments

AlexCuse · Accepted Answer · 2009-02-02 16:16:40Z

0

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

answered Feb 2, 2009 at 16:16

AlexCuse

18.4k5 gold badges44 silver badges52 bronze badges

Comments

sth · Accepted Answer · 2010-02-23 18:58:43Z

0

Single quotes to delimit text in SQL:

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

edited Feb 23, 2010 at 18:58

sth

231k56 gold badges288 silver badges370 bronze badges

answered Feb 23, 2010 at 12:25

JFTxJ

1

Collectives™ on Stack Overflow

Replace Multiple Strings in SQL Query

7 Answers 7

2 Comments

1 Comment

Comments

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

7 Answers 7

2 Comments

1 Comment

Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related