How to replace a string with values from columns i

问题:

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

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?

回答1:

The key is using two replace functions.

Update tablename
   set exp = replace (replace (exp, 'W.Major', major), 'W.Start', start);

Read more about replace on MSDN. This function is more or less the same across all major RDBMSes.

EDIT:

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:

1. 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.
   

2. 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) From the first row to the last row in your main table, select the column 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) Prepare a dynamic SQL statement which would then write an update query with nested replace calls for you. Execute this dynamic SQL.