Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

Solved

sql replace - words from another table

Posted on 2013-12-19

Microsoft SQL Server 2008

4 solutions

Medium Priority

376 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?

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

Join The Community

5 Comments

LVL 39

Assisted Solution

by:Aaron Tomosky

Aaron Tomosky earned 500 total points

ID: 397309762013-12-19

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

LVL 38

Assisted Solution

by:Jim P.

Jim P. earned 500 total points

ID: 397309912013-12-19

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.

LVL 12

Assisted Solution

by:Saurabh Bhadauria

Saurabh Bhadauria earned 500 total points

ID: 397311372013-12-19

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

LVL 51

Accepted Solution

by:Mark Wills

Mark Wills earned 500 total points

ID: 397316242013-12-20

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

Author Closing Comment

by:johnson1

ID: 397320732013-12-20

thank you for your help

Featured Post

Question has a verified solution.

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

SQL Server database migration - The Copy Database Wizard method

Article by: Vitor

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.

How to recover deleted rows in SQL Server

Article by: Yashwant

In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.

Microsoft Bookings Review

Video by: Lisa

This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.

How to Create Calendar Reports from Access Queries

Video by: crystal

Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data! ... really see it To use the code to create a calendar from a q…