Solved

sql replace - words from another table

Posted on 2013-12-19
5
370 Views
Last Modified: 2013-12-20
Hello,

I want to find all words in @StringTable where matching words are in Word column in @TableReplacelist and replace it with value from ReplaceWord column.

DECLARE @TableReplacelist table(Word varchar(50), ReplaceWord varchar(50))
INSERT INTO @TableReplacelist(Word,ReplaceWord)values('One','Eins')
INSERT INTO @TableReplacelist(Word,ReplaceWord)values('Two','Zwei')
INSERT INTO @TableReplacelist(Word,ReplaceWord)values('Three','Drei')
INSERT INTO @TableReplacelist(Word,ReplaceWord)values('Four','Vier')

DECLARE @StringTable table(Id int, MyString varchar(max))
INSERT INTO @StringTable(Id,MyString)values(1,'Here is One and Three')
INSERT INTO @StringTable(Id,MyString)values(2,'Here is One and Four')

If I would select all from @StringTable it should be modify like this:
1  Here is Eins and Drei
2  Here is Eins and Vier

Can you please suggest the best approach for this?
0
Comment
Question by:johnson1
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 39

Assisted Solution

by:Aaron Tomosky
Aaron Tomosky earned 125 total points
ID: 39730976
Something like this:
Update string table set my string = replace(my string, search, replace)

I think search and replace and be a select. You just wanted a good start so this is from memory
0
 
LVL 38

Assisted Solution

by:Jim P.
Jim P. earned 125 total points
ID: 39730991
You are essentially going to have to pick a master table and then iterate through it row by row, Your best bet is something like:

Declare @I as Integer
Declare @Count as Integer
Declare @SQL as Varchar(1000)
Declare @Word as Varchar(50)
Declare @ReplaceWord as Varchar(50)


DECLARE @TableReplacelist table(Word varchar(50), ReplaceWord varchar(50), RowNum long Identity(1,1))
INSERT INTO @TableReplacelist(Word,ReplaceWord)values('One','Eins')
INSERT INTO @TableReplacelist(Word,ReplaceWord)values('Two','Zwei')
INSERT INTO @TableReplacelist(Word,ReplaceWord)values('Three','Drei')
INSERT INTO @TableReplacelist(Word,ReplaceWord)values('Four','Vier')

SELECT @Count = Max(RowNum ) from @TableReplacelist
SELECT  @I = 1

While @I <= @Count 
Begin
SELECT  @Word = Word,   @ReplaceWord = ReplaceWord FROM @TableReplacelist
WHERE  RowNum = @I

@SQL = 'UPDATE  @StringTable SET MyString = REPLACE(MyString,' +  CHAR(39) + @Word +   CHAR(39) + ','  +  CHAR(39) + @ReplaceWord  +  CHAR(39) + ')'

EXEC @SQL

SELECT  @I  = @I + 1
End

Open in new window

Note that this aircoding. Never tested, but in the general idea.
0
 
LVL 12

Assisted Solution

by:Saurabh Bhadauria
Saurabh Bhadauria earned 125 total points
ID: 39731137
You need an udf..

First create an UDF like this..
create function udf_replace(@MyString varchar(max))
returns varchar(max)
as begin 

DECLARE @TableReplacelist table(Word varchar(50), ReplaceWord varchar(50))
INSERT INTO @TableReplacelist(Word,ReplaceWord)values('One','Eins')
INSERT INTO @TableReplacelist(Word,ReplaceWord)values('Two','Zwei')
INSERT INTO @TableReplacelist(Word,ReplaceWord)values('Three','Drei')
INSERT INTO @TableReplacelist(Word,ReplaceWord)values('Four','Vier')


select @MyString=replace(@MyString,word,replaceword) from @TableReplacelist

return @MyString

end 

 

Open in new window


Then run below code to achieve result

 
DECLARE @StringTable table(Id int, MyString varchar(max))
INSERT INTO @StringTable(Id,MyString)values(1,'Here is One and Three')
INSERT INTO @StringTable(Id,MyString)values(2,'Here is One and Four')

select mystring , dbo.udf_replace(mystring) from @StringTable

Open in new window



Thanks,
Saurv
0
 
LVL 51

Accepted Solution

by:
Mark Wills earned 125 total points
ID: 39731624
Why table variables ?

dynamic SQL will have problems... While you can pass a table variable (by first creating a custom type) you cannot update.

How many rows would there be realistically (in each table) ? If not too many, you could do it all in one dynamic SQL statement.

But, you need to convert to temp table and then use :

DECLARE @TableReplacelist table(Word varchar(50), ReplaceWord varchar(50))
INSERT INTO @TableReplacelist(Word,ReplaceWord)values('One','Eins')
INSERT INTO @TableReplacelist(Word,ReplaceWord)values('Two','Zwei')
INSERT INTO @TableReplacelist(Word,ReplaceWord)values('Three','Drei')
INSERT INTO @TableReplacelist(Word,ReplaceWord)values('Four','Vier')

IF OBJECT_ID('tempdb..#stringtable','U') is NOT NULL drop table #StringTable
CREATE TABLE #StringTable (Id int, MyString varchar(max))
INSERT INTO #StringTable(Id,MyString)values(1,'Here is One and Three')
INSERT INTO #StringTable(Id,MyString)values(2,'Here is One and Four')
 
 -- npw buil our dynamic SQL

declare @sql nvarchar(max)
select @sql = isnull(@sql+' ; ','') + 'update #stringtable set mystring = replace(mystring,'''+word+''','''+replaceword+''') where charindex('''+word+''',mystring) > 0 '
from @TableReplacelist
--print @sql

execute sp_executesql @sql

select * from #stringtable

Open in new window


Now, if getting too big, then could leave out the "where" test for charindex

But if you can let me know about table sizes, and tablevar, can come up with an alternate plan of attack...
0
 

Author Closing Comment

by:johnson1
ID: 39732073
thank you for your help
0

Featured Post

Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Query Syntax 17 50
read Json fields and insert all the content into a table by Store Procedure 4 22
How come this XML node is not read? 3 27
SQL syntax question 6 38
Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

761 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question