blocks|key|3988312|text|如果您有许多不同的值要替换，则应该创建一个表来存储旧值到新值的映射。然后，您可以在查询中联接该表。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3988313|下面的SQL用新值替换旧值(如果未定义转换，则保留原值)：|3988314|SELECT+
++++COALESCE(m.new_value,+t.value)
FROM+
++++table+t
++++LEFT+JOIN+mapping_table+m+
++++++++ON+t.value+=+m.old_value|code-block|syntax|javascript|3988315|对于有限数量的要翻译的值，可以大小写。或者使用联合的CTE：|3988316|WITH+mapping_table+AS+(
++++SELECT+'LOT_NUMBER'+AS+old_value,+'Lot+Number'+AS+new_value
++++SELECT+'LOT_NUMBER_XREF',+'Lot+Number+Cross-Reference'
)
SELECT+
++++COALESCE(m.new_value,+t.value)
FROM+
++++table+t
++++LEFT+JOIN+mapping_table+m+
++++++++ON+t.value+=+m.old_value|3988317|entityMap^0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|P|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|Q|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|R|8|@]|9|@]|A|$]]|$1|K|3|L|5|F|7|S|8|@]|9|@]|A|$G|H]]|$1|M|3|-4|5|6|7|T|8|@]|9|@]|A|$]]]|N|$]]

If you have many different values to replace, you should create a table to store the mapping of old values to new values. Then you can join that table in your query. 

The below SQL replaces the old value with the new value (or leaves it as is if no translation was defined) :

<pre><code>SELECT 
 COALESCE(m.new_value, t.value)
FROM 
 table t
 LEFT JOIN mapping_table m 
 ON t.value = m.old_value
</code></pre>

For a limited number of values to translate you can CASE. or maybe a CTE with UNION :

<pre><code>WITH mapping_table AS (
 SELECT 'LOT_NUMBER' AS old_value, 'Lot Number' AS new_value
 SELECT 'LOT_NUMBER_XREF', 'Lot Number Cross-Reference'
)
SELECT 
 COALESCE(m.new_value, t.value)
FROM 
 table t
 LEFT JOIN mapping_table m 
 ON t.value = m.old_value
</code></pre>

blocks|key|3984664|text|如果你不能使用交叉引用表，可以使用(你必须在你的应用程序中生成)子查询或cte！CTE：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|3984665|with+mapping+as+(
+select+'LOT_NUMBER'+as+old_value,+'Lot+Number'+as+new_value
+union+all+select+'LOT_NUMBER_XREF',+'Lot+Number+Cross-Reference'
)
select+
+t.value,
+coalesce(m.new_value,+'none')+as+new_value
from+tbl_your_table+t
left+join+mapping+m+on+t.value+=+m.old_value|code-block|syntax|javascript|3984666|或SubQuery：|3984667|select+
+t.value,
+coalesce(m.new_value,+'none')+as+new_value
from+tbl_your_table+t
left+join+(
+select+'LOT_NUMBER'+as+old_value,+'Lot+Number'+as+new_value
+union+all+select+'LOT_NUMBER_XREF',+'Lot+Number+Cross-Reference'
)+m+on+t.value+=+m.old_value|3984668|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|N|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|O|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

If you can't use cross-reference table, use (you'll have to generate it in your app) sub-query or cte instead!
CTE: 

<pre><code>with mapping as (
 select 'LOT_NUMBER' as old_value, 'Lot Number' as new_value
 union all select 'LOT_NUMBER_XREF', 'Lot Number Cross-Reference'
)
select 
 t.value,
 coalesce(m.new_value, 'none') as new_value
from tbl_your_table t
left join mapping m on t.value = m.old_value
</code></pre>

Or SubQuery:

<pre><code>select 
 t.value,
 coalesce(m.new_value, 'none') as new_value
from tbl_your_table t
left join (
 select 'LOT_NUMBER' as old_value, 'Lot Number' as new_value
 union all select 'LOT_NUMBER_XREF', 'Lot Number Cross-Reference'
) m on t.value = m.old_value
</code></pre>

blocks|key|3988444|text|如果你有一些通用的东西，比如_字符，那么你可以使用LIKE运算符和|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|3988445|+++++++++++++SELECT+replace(dummy,'_','+')+from+table+where+
+++++++++++++dummy+like+'%25_%25'+++--+check+generic+regex|code-block|syntax|javascript|3988446|然后，在此基础上使用CASE+WHEN和DUMMY+IN(X,REF)+Then+REPLACE(DUMMY,'X',CROSS)，对于其他情况也是如此，这将减少案例的数量。|3988447|问题是只需要检查泛化的术语和它们的替换项，存储它，IN子句，预过滤最一般的术语。如果计数仍然很大，则需要一个临时表用于查找引用，就像其他编程语言中的数组一样|3988448|entityMap^0|E|1|P|4|0|0|A|9|K|5|Q|13|0|P|2|0^^$0|@$1|2|3|4|5|6|7|Q|8|@$9|R|A|S|B|C]|$9|T|A|U|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|V|8|@]|D|@]|E|$I|J]]|$1|K|3|L|5|6|7|W|8|@$9|X|A|Y|B|C]|$9|Z|A|10|B|C]|$9|11|A|12|B|C]]|D|@]|E|$]]|$1|M|3|N|5|6|7|13|8|@$9|14|A|15|B|C]]|D|@]|E|$]]|$1|O|3|-4|5|6|7|16|8|@]|D|@]|E|$]]]|P|$]]

If you have something generic like <code>_</code> charecter then what you can do is use <code>LIKE</code> operator and 

<pre><code> SELECT replace(dummy,'_',' ') from table where 
 dummy like '%_%' -- check generic regex
</code></pre>

then, use <code>CASE WHEN</code> on top of this with <code>DUMMY</code> <code>IN(X,REF) Then REPLACE(DUMMY,'X',CROSS)</code> likewise for the others as well this will reduce the no of cases.

The thing is just check the generalised terms and their replacements store it <code>IN</code> clause prefiltering the most general ones. If the count is still large you need a temporary table for lookup reference like array in other programming languages

I want to replace certain values with corresponding values in SQL. I cannot create a cross-reference table. The values can be specified in 'txt' or 'properties' file in my application

I looked at <code>CASE</code> statement but I have 200 replacements to make for which <code>CASE</code> is not sufficient

<pre><code>SELECT dummy FROM TestTable;
</code></pre>

In my answer if I get <code>LOT_NUMBER</code> I want to replace it with <code>Lot Number</code> or if I get <code>LOT_NUMBER_XREF</code> I want to replace it with <code>Lot Number Cross-Reference</code>

<pre><code>dummy
LOT_NUMBER
LOT_NUMBER_XREF
</code></pre>

to be replaced with 

<pre><code>dummy
Lot Number
Lot Number Cross-Reference
</code></pre>

Map values in column to values in SQL

我想用SQL中的相应值替换某些值。我无法创建交叉引用表。这些值可以在我的应用程序的'txt‘或'properties’文件中指定我查看了CASE语句，但有200个替换项需要进行替换，而CASE是不够的SELECT dummy FROM TestTable;在我的回答中，如果我得到LOT_NUMBER，我想用Lot Nu...

问将列中的值映射到SQL中的值
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问将列中的值映射到SQL中的值EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问将列中的值映射到SQL中的值
EN