数据应按照查找表进行替换

问题描述

以下是我用于测试和构建查询的sqlfiddle:

Following is my sqlfiddle for testing and building the query:

http://sqlfiddle.com/#!2/975c6/1

我一直想做的是检测Table1列ADRES`house#240 st. #76 Cantt.芝加哥路包含查找表中错误列中存在的任何值，应将其替换为正确的列值.

What I have been trying to do is to detect if Table1 column ADRES `house# 240 st. # 76 Cantt. road Chicago contains any value present in wrong column in lookup table that should get replaced by right column value.

表1:

Username | Adres
Jhon     | house#  240 st. #  76 Cantt. road Chicago 

查询表格:

WRONG   |    Right
house#       HNO

例如:house# 240 st. # 76 Cantt. road Chicago(房屋编号错误，应将其替换为HNO)并更新Table 1并将地址设置为

For example : house# 240 st. # 76 Cantt. road Chicago (house# is wrong so it should gets replaced by HNO)and update Table 1 and set address to

Jhon     | HNO  240 st. #  76 Cantt. road Chicago

推荐答案

如果要更新字段，

 UPDATE table1 a CROSS JOIN lookup b
 SET    a.ADRES = REPLACE(a.ADRES, b.`WRONG`, b.`RIGHT`)
 WHERE  a.ADRES LIKE CONCAT('%', b.`WRONG`, '%')

• SQLFiddle演示
• SQLFiddle Demo

但是如果您只想投影替换的值，

but if you want only to project the replaced value,

 SELECT  a.Username, REPLACE(a.ADRES, b.`WRONG`, b.`RIGHT`)
 FROM    table1 a CROSS JOIN lookup b
 WHERE   a.ADRES LIKE CONCAT('%', b.`WRONG`, '%')

• SQLFiddle演示
• SQLFiddle Demo

这篇关于数据应按照查找表进行替换的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！