替换 SQL 查询中的多个字符串
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/503673/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Replace Multiple Strings in SQL Query
提问by Eric Ness
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
我正在 SQL Server 中编写一个 SQL 查询，其中我需要用一个字符串值替换多个字符串值。例如
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。
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.
编辑：产品类别中可能还有其他值。请参阅上面的编辑示例。
回答by Joel Coehoorn
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:
到目前为止，BradC 有最好的答案，但如果您由于某种原因无法创建附加表，我想发布对 Kibbee 答案的改编：
SELECT
CASE WHEN Product IN ('Banana', 'Apple', 'Orange') Then 'Fruit'
ELSE Product END
FROM [Table]
回答by BradC
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.
然后只需进行内部联接。
回答by Kibbee
Select
Case Product WHEN 'Banana' Then 'Fruit'
WHEN 'Apple' Then 'Fruit'
WHEN 'Orange' Then 'Fruit'
ELSE Product
END
FROM Table
回答by Adam Ralph
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
回答by Rockcoder
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 子句
WHERE Product IN ('Banana', 'Orange', 'Apple')
回答by AlexCuse
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
有关示例，请查看此链接： 数据属于您的表 - 不在您的代码中
回答by JFTxJ
Single quotes to delimit text in SQL:
在 SQL 中分隔文本的单引号：
CASE WHEN ShiptoPlant IN (';','/',' ') Then '' ELSE ShipToPlant END
