使用另一列中的值替换一列中的值

I have two database tables, in one of them I have some messages (like a template) and in the second one I have the values that should be replaced in the message.

我有两个数据库表，其中一个我有一些消息（如模板），在第二个我有消息中应该替换的值。

I'll try to explain myself

我会试着解释一下自己

In table A I have something like this

在表A中，我有类似的东西

Id - Message

Id - 消息

1 - "User {0} has logon from {1}"

1 - “用户{0}已从{1}登录”

In table B I have something like this

在表B中，我有类似的东西

Id - Id Message - ParamValue - ParamPosition

Id - Id消息 - ParamValue - ParamPosition

1 - 1 - Hugo - 0

1 - 1 - 雨果 - 0

2 - 1 - Computer A - 1

2 - 1 - 计算机A - 1

What I would like to have is a message formed like

我想要的是一条形成的信息

User Hugo has logon from Computer A

用户Hugo已从计算机A登录

Something like string.format in c#.

类似于c＃中的string.format。

How can I do this and how can I do it no matter how many parameters does the message have?

我怎么能这样做，无论消息有多少参数，我怎么能这样做？

Thanks for your help!

谢谢你的帮助！

2 个解决方案

#1

As @Gordon Linoff pointed out in the comments MySQL is not suited for such tasks. If however your Message Table is somewhat "constant" you could try to replace the values like this:

正如@Gordon Linoff在评论中指出的那样，MySQL不适合这样的任务。但是，如果您的消息表有点“不变”，您可以尝试替换这样的值：

SELECT
  REPLACE(REPLACE(messages.message, '{0}', first_param.ParamValue), 
     '{1}', second_param.ParamValue)
FROM
  messages LEFT JOIN params first_param ON (first_param.message_id = messages.id)
           LEFT JOIN params second_param ON (second_param.message_id = messages.id)

But beware, that solution will only work for at most two parameters.

但请注意，该解决方案仅适用于最多两个参数。

#2

below code can be used to get the desired result even if you have more than one rows in tableA.

即使在tableA中有多行，下面的代码也可用于获得所需的结果。

;WITH CTE 
AS
(
    select Msgid,[0] as position0,[1]  as position1
    from
    (
        SELECT Msgid,paramvalue,position
        FROM #tableB
    )pv
    pivot 
    (
       max(paramvalue) FOR position IN ([0],[1])
    )P

)

SELECT
  REPLACE(REPLACE(om.Msg, '{0}', pm.position0),'{1}', pm.position1)
from #tableA om
left join CTE pm ON om.id = pm.Msgid

#1