如何用SQL中表中列的值替换字符串 [英] How to replace a string with values from columns in a table in SQL
问题描述
我有一张像下面这样的表格
I have a table like the following
Exp Major Start
---------------------------------- ----------- -------
My names ar W.Major and W.Start Hal Bark
W.Major is a doctor Mark Slope
我想编写一个 SQL 查询，用 Major 和 Start<列中的值替换 exp 列中的 W.Major 或 W.start.
I want to write a SQL query to replace W.Major or W.start in the exp column with the values in the columns Major and Start.
有人可以建议我这样做吗?
Can someone please suggest me a way to do this?
推荐答案
关键是使用了两个replace函数.
The key is using two replace functions.
Update tablename
set exp = replace (replace (exp, 'W.Major', major), 'W.Start', start);
在 MSDN 上阅读有关 替换的更多信息.此功能在所有主要 RDBMS 中或多或少都相同.
Read more about replace on MSDN. This function is more or less the same across all major RDBMSes.
如果您不知道文本中有多少关键字和替换列，我建议您创建一个存储过程，在其中执行以下操作:
In a scenario where you don't know how many keywords and replacement columns are there in your text, I'd suggest you create a stored procedure in which you do the following:
使用具有两列的临时表 - 一列用于搜索键，例如
'W.Major'，另一个带有列名，例如'主要'.请注意，您不必存储该列中的值，只需存储该列的名称.像这样:-
Use a temporary table that has two columns- one for the search key, e.g.
'W.Major', and the other with the name of the column , e.g.'major'. Note that you don't have to store the values from that column, just the name of the column. Like this:-
key replacement_col
---------- -----------------
W.Major major
W.Start start
... and so on.
现在用主表和临时表构建一个动态 SQL 循环.以您觉得舒服的方式循环.我建议使用以下循环方法:
Now build a dynamic SQL looping in with your main table and this temporary table. Loop the way you find comfortable. I would suggest the following method of looping:
a) 从主表的第一行到最后一行，选择 exp 列.
a) From the first row to the last row in your main table, select the column exp.
b) 循环遍历 exp 的这个值来搜索任何搜索关键字.在临时表中查找关键字和值.
b) Loop through this value of exp to search for any search keywords. Look for the keywords and values in your temp table.
c) 准备一个动态 SQL 语句，然后它会为您编写一个带有嵌套 replace 调用的更新查询.执行这个动态 SQL.
c) Prepare a dynamic SQL statement which would then write an update query with nested replace calls for you. Execute this dynamic SQL.
这篇关于如何用SQL中表中列的值替换字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
