November 24, 2009 at 2:50 pm
I have a column in my database that contains a URL like this:
Unfortunately, the IP has changed and about 2000 rows will be affected.
Is there a way of using SELECT REPLACE to SELECT all rows that contain the old IP and then somehow REPLACE them with the new IP? Keeping the rest of the URL intact?
Thanks!
November 24, 2009 at 2:57 pm
Where are the changed IP address values?
The command that's required to update needs to look for the new IP details, where does it find those new IP?
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
November 24, 2009 at 3:04 pm
declare @oldip varchar(100)
declare @newip varchar(100)
select distinct oldip
, newIp
, 0 processed
into #ips
from tableName
while exists ( select top 1 * from #ips where processed = 0 )
begin
select top 1 @oldip = oldip , @newip = newip from #ips where processed = 0
update tableWhoseDataIsChanging
set url = replace(url, @oldip, @newip)
where url like '%'+@oldip + '%'
update #ips
set processed = 1
where oldIp = @oldIp
and newIp = @newIp
end
drop table #ips
November 25, 2009 at 12:45 am
Is this what you are looking for?
Create table #t (ip varchar(1000))
INSERT INTO #t VALUES ('http://192.49.42.39/bcccontent')
INSERT INTO #t VALUES ('http://192.49.42.39/bcccontent1')
INSERT INTO #t VALUES ('http://192.49.42.39/bcccontent2')
INSERT INTO #t VALUES ('http://200.49.42.39/bcccontent1')
Select REPLACE(ip, '192.49.42.39','192.49.42.40') from #t
Where charindex('192.49.42.39', ip) > 0
I mean ofcourse you can work out update based on this!
---------------------------------------------------------------------------------
November 25, 2009 at 1:27 am
Isnt this what a DNS service is designed to do ?
Seems to me bad practice to use address precisely for this reason.
November 25, 2009 at 6:26 am
Hi guys, thanks for the suggestions!
Would something as simple as this work?
UPDATE myTable
SET linkURL = REPLACE(linkURL, 'oldIP', 'newIP')
Thanks!
November 25, 2009 at 6:41 am
Hi guys, thanks for the suggestions!
Would something as simple as this work?
UPDATE myTable
SET linkURL = REPLACE(linkURL, 'oldIP', 'newIP')
----------------------------------------------------------------------------------------
--not really because that would replace anywhere in the linkurl that had the word oldIp and replace it w the word newIP.
there is a ton of ways to do it to be honest. I assume u have a list of old and new ip's? Are they stored in a database table somwhere? if so what is the table name and column names? Or is this information in excel or something? That list is the key to this problem. If it's in a database table then it will be very easy.
November 25, 2009 at 7:19 am
Oh Im sorry, for oldIp I actually meant something like this:
UPDATE myTable
SET linkURL = REPLACE(linkURL, '192.49.42.39', '192.49.21.44')
November 25, 2009 at 9:12 am
yes that will do it.
November 25, 2009 at 11:50 am
Magy (11/25/2009)
Oh Im sorry, for oldIp I actually meant something like this:UPDATE myTable
SET linkURL = REPLACE(linkURL, '192.49.42.39', '192.49.21.44')
Actually, you should include the WHERE clause that Nabha used in his nearly identical suggestion.
--Jeff Moden
November 25, 2009 at 11:53 am
john scott miller (11/24/2009)
declare @oldip varchar(100)
declare @newip varchar(100)
select distinct oldip
, newIp
, 0 processed
into #ips
from tableName
while exists ( select top 1 * from #ips where processed = 0 )
begin
select top 1 @oldip = oldip , @newip = newip from #ips where processed = 0
update tableWhoseDataIsChanging
set url = replace(url, @oldip, @newip)
where url like '%'+@oldip + '%'
update #ips
set processed = 1
where oldIp = @oldIp
and newIp = @newIp
end
drop table #ips
John... I've got to ask and I'm not being snotty about this, but why did you feel a loop was necessary for this? The reason why I ask is that a lot of folks will use a loop for something like this and it's becoming more important to me as to why they think that way so maybe I can write a "primer" on how to avoid the loop to go along with several articles that one of the other heavy hitters has written on the subject.
--Jeff Moden
November 26, 2009 at 7:06 am
Jeff, I had wondered that as well. If the new IP's are stored in a table with the old IP's, it would seem simpler to just do an update statement similar to this:
update URLtable set URL = REPLACE(URL, b.oldip, b.newip) --I may have the syntax wrong here
from URLtable a join NEWIPTable b on substring(a.URL, code to find positions) = b.oldip
where some criteria
Sorry for the lack of completeness, I'm trying to get out of here for a week off.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply