Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users
Course Of The Month
Become a Premium Member and unlock a new, free course in leading technologies each month.
Solved
sql replace - words from another table
Posted on 2013-12-19
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,Rep
INSERT INTO @TableReplacelist(Word,Rep
INSERT INTO @TableReplacelist(Word,Rep
INSERT INTO @TableReplacelist(Word,Rep
DECLARE @StringTable table(Id int, MyString varchar(max))
INSERT INTO @StringTable(Id,MyString)v
INSERT INTO @StringTable(Id,MyString)v
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?
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,Rep
INSERT INTO @TableReplacelist(Word,Rep
INSERT INTO @TableReplacelist(Word,Rep
INSERT INTO @TableReplacelist(Word,Rep
DECLARE @StringTable table(Id int, MyString varchar(max))
INSERT INTO @StringTable(Id,MyString)v
INSERT INTO @StringTable(Id,MyString)v
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?
[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
Active today
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
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
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
You need an udf..
First create an UDF like this..
Then run below code to achieve result
Thanks,
Saurv
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
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
Thanks,
Saurv
Active 2 days ago
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 :
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...
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
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...
Featured Post
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
Hi all,
It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
In this video, viewers are given an introduction to using the Windows 10 Snipping Tool, how to quickly locate it when it's needed and also how make it always available with a single click of a mouse button, by pinning it to the Desktop Task Bar.
Int…
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…
Suggested Courses
Earn Certification
HTML5 Specialist - Certification
Free withPremium
Course of the Month4 days, 11 hours left to enroll
Earn Certification
Administering Microsoft SQL Server 2012 Databases (Exam 70-462)
$79.00$71.10
636 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.