Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
843 views
in Technique[技术] by (71.8m points)

Bulk Replace in SQL-Server

Is it possible to do bulk replace without while loop or what is the best way

Table-1

+-------+--------+
| name  |  value |
+-------+--------+
| @1@   |   one  |
| @2@   |   two  |
| @3@   |  three |
+-------+--------+

Table-2 (updated: there is more than one different tokens in table2)

+-----------------------+
|    col1               |
+-----------------------+
| string @1@ string @2@ |
| string @2@ string @1@ |
| string @3@ string @2@ |
+-----------------------+

I like to replace all token from Table-2 with Table-1's value column respectively.

Expected Result

+-------------------------+
|    col1                 |
+-------------------------+
| string one string two   |
| string two string one   |
| string three string two |
+-------------------------+

Current solution with While loop

declare @table1 table(name nvarchar(50),value nvarchar(50)) 
insert into @table1 values('@1@','one'),('@2@','two'),('@1@','three')

declare @table2 table(col1 nvarchar(50)) 
insert into @table2 values('string @1@ string @2@'),('string @2@ string @1@'),('string @3@ string @2@')

WHILE EXISTS (SELECT 1 FROM @table2 t2 INNER JOIN @table1 t1 ON CHARINDEX(t1.name,[col1])>0)
BEGIN
    UPDATE @table2
    SET col1=REPLACE(col1,name,value)
    FROM @table1
    WHERE CHARINDEX(name,[col1])>0  
END

select * from @table2

Thanks

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

I suppose you use Sql Server (you've tagged with tsql):

I've run this query on Sql fiddle with 2012 version, but on my PC I've tried with 2008r2 version.

You can procede in this way:

UPDATE table2
SET col1 = REPLACE(col1, 
(SELECT name FROM table1 WHERE col1 LIKE '%' + table1.NAME + '%'),
(SELECT value FROM table1 WHERE col1 LIKE '%' + table1.NAME + '%'))

Sql Fiddle

If you want show only the value without UPDATE you can proceed in this way:

SELECT REPLACE(T2.col1, T1.name, T1.value)
FROM table1 T1
JOIN table2 T2
ON T2.col LIKE '%' T1.name + '%'

EDIT

After editing of question / comment my answer is not complete because on the same row can exist more one token. I'm thinking... :)

I thought: :D

IMHO: You must create a loop on your table because the presence of several tokens don't resolve with a single UPDATE statement with subquery, because as you written, the subquery return more than one value.

In Sql Server the REPLACE function change only token, so if you want change in one step two token you must nest your REPLACE function, but we have a number undefined of token in a row so we can't prevent to apply the exact number of REPLACE. An help you can have using a cursor and a dynamic SQL query build on runtime, so you can do a single UPDATE per row. If you want a guide line to use CURSOR and DYNAMIC SQL, please write me. Good night ;)


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...