Re: Text replacing from another table
- From: "Tom Cooper" <tomcooper@xxxxxxxxxxx>
- Date: Fri, 10 Jul 2009 01:39:15 -0400
I agree with Plamen, it would be helpful if you told us what the result you
wanted was. I assume with the sample data you gave, the result you want is:
Table1 - Data
[PK_ID] [Name]
1 Joey's Savings
2 Gerry's Savings
3 Dominic's Savings
Is that correct?
Plamen, your new solution doesn't seem to work when I run it in SQL 2005 SP3
or SQL2008 SP1. I think it's because no update statement can update the
same row in a table more than once. (The same problem that shows up with
Microsoft's extension to the UPDATE statement allowing
UPDATE <table1> FROM <table1 INNER JOIN <table2> ON ...
and the inner join has more than one match.)
Julian, one way you could do it is with dynamic SQL. For example, use FOR
XML PATH('') to load a nvarchar variable an update statement with embedded
REPLACE functions - In your example it would look like
Update Data Set Name = Replace(Replace(Name, 'Steven''s', 'Joey''s'),
'Bank', 'Savings);
and then use dynamic sql to execute that statement. Doing this may expose
you to SQL injection attacks. It should be very efficient, but, depending
on how entries get placed into ReplacementCriteria, you need to check very
carefully for SQL injection. If you want to do this way, then it could look
like
Declare @Sql nvarchar(MAX);
With cte As (
Select (Select 'Replace('
From ReplacementCriteria
For XML Path('')) As Replaces,
(Select ', ''' + Replace(search_for, '''', '''''') + ''', '''+
Replace(replace_with, '''', '''''') + ''')'
From ReplacementCriteria
Order By keycol
For XML Path('')) As ReplaceList
)
Select @Sql = 'Update Data Set name = '
+ Cast(Replaces As nvarchar(max))
+ 'name'
+ Cast(ReplaceList As nvarchar(max))
From cte;
Exec sp_executesql @Sql;
You didn't say how many rows will be in ReplacementCriteria. But you may
want to consider just doing this with a cursor. Writing a cursor to do this
is straight-forward, and easy to maintain. The problem with cursors, as you
know, is that they are often slow. Sometimes, however, they are the best
solution. If you only have a few rows in ReplacementCriteria, and/or this
is a one time or a very rare process, the cursor may be an acceptable
solution.
Tom
"Plamen Ratchev" <Plamen@xxxxxxxxxxxxx> wrote in message
news:u3yf7MOAKHA.3708@xxxxxxxxxxxxxxxxxxxxxxx
It is really not clear what you mean, it may be best to illustrate with
example. If you are saying that there should not be a join on the key
column, then simply change that to a cross join to apply to all rows:
WITH UpdateCTE AS (
SELECT name, REPLACE(name, search_for, replace_with) AS new_name
FROM Data AS D
CROSS JOIN ReplacementCriteria AS R)
UPDATE UpdateCTE
SET name = new_name;
--
Plamen Ratchev
http://www.SQLStudio.com
.
- Follow-Ups:
- Re: Text replacing from another table
- From: Plamen Ratchev
- Re: Text replacing from another table
- References:
- Text replacing from another table
- From: stjulian
- Re: Text replacing from another table
- From: Plamen Ratchev
- Re: Text replacing from another table
- From: amish
- Re: Text replacing from another table
- From: stjulian
- Re: Text replacing from another table
- From: stjulian
- Re: Text replacing from another table
- From: Plamen Ratchev
- Text replacing from another table
- Prev by Date: Re: Debating Views vs Stored Procedures for reports and applications
- Next by Date: Re: What to know who is using the DB
- Previous by thread: Re: Text replacing from another table
- Next by thread: Re: Text replacing from another table
- Index(es):
Relevant Pages
|
