Replacing values from one column with values from another

Question

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

Id - Message

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

In table B I have something like this

Id - Id Message - ParamValue - ParamPosition

1 - 1 - Hugo - 0

2 - 1 - Computer A - 1

What I would like to have is a message formed like

User Hugo has logon from Computer A

Something like string.format in c#.

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

Thanks for your help!

I don't think you can do this with a query in MySQL. You can do it with a stored procedure using `while`. — Gordon Linoff, Feb 02 '16 at 12:01
See http://stackoverflow.com/questions/1262786/mysql-update-query-based-on-select-query for a solution — Aleksi Yrttiaho, Feb 02 '16 at 12:07

score 0 · Answer 1 · answered Feb 02 '16 at 12:24

0

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:

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.

answered Feb 02 '16 at 12:24

Martin Schneider

3,268
4
19
29

Any idea on how to do it if I have "x" parameters? Some messages can have only one and others could have more than one. I was thinking on maybe examine every character in the message and see if it is a number, if it is i'll search the appropriate value...but I don't think this is a good way – Hugo Assanti Feb 03 '16 at 10:44

score 0 · Answer 2 · edited Feb 03 '16 at 12:00

below code can be used to get the desired result even if you have more than one rows in 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

Replacing values from one column with values from another

2 Answers2