SELECT and REPLACE query

Question

Post reply

SELECT and REPLACE query

Magy

SSCarpal Tunnel

Points: 4217
More actions
November 24, 2009 at 2:50 pm

#214058

I have a column in my database that contains a URL like this:
http://192.49.42.39/bcccontent/a8dfeeeb39-ffdd-46b6-cccc-39fba4605c86/rrrr_2009_2008-01-12_09-01-AM.htm
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!

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Bru Medishetty SSC-Insane Points: 23606 More actions · Answer 1

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?

Bru Medishetty

Blog -- LearnSQLWithBru

Join on Facebook Page Facebook.comLearnSQLWithBru

Twitter -- BruMedishetty

BaldingLoopMan SSCertifiable Points: 6378 More actions · Answer 2

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

Nabha SSCrazy Eights Points: 8534 More actions · Answer 3

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!

---------------------------------------------------------------------------------

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 4

Isnt this what a DNS service is designed to do ?

Seems to me bad practice to use address precisely for this reason.

Clear Sky SQL
My Blog[/url]

Magy SSCarpal Tunnel Points: 4217 More actions · Answer 5

Hi guys, thanks for the suggestions!

Would something as simple as this work?

UPDATE myTable

SETlinkURL = REPLACE(linkURL, 'oldIP', 'newIP')

Thanks!

BaldingLoopMan SSCertifiable Points: 6378 More actions · Answer 6

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.

Magy SSCarpal Tunnel Points: 4217 More actions · Answer 7

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')

BaldingLoopMan SSCertifiable Points: 6378 More actions · Answer 8

BaldingLoopMan

SSCertifiable

Points: 6378

November 25, 2009 at 9:12 am

#1084140

yes that will do it.

Jeff Moden SSC Guru Points: 1004286 More actions · Answer 9

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

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004286 More actions · Answer 10

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

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Ross McMicken SSCarpal Tunnel Points: 4435 More actions · Answer 11

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.