Update Query

Question

Post reply

Update Query

Mark Johns

Valued Member

Points: 53
More actions
July 12, 2005 at 9:17 am

#67734

Hi,
I am looking to update a field in an SQL DB. Then thing is its a UNC path and I only want to update the first part. e.g.
Change \\server1\folder1\1234 to \\server2\folder5\1234 (Keeping the original folder names(the 1234 part as this is an account number). There are thousands of records and I need to select records on the folder part of the UNC path. I dont think I am able to do it!
Any help would be most helpful!
Mark.

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

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

noeld SSC Guru Points: 96590 More actions · Answer 1

can you post some data samples ?

this is a wild guess

Update Table Set Fld = '\\server2\folder5\' + Right(Fld,charindex('\',1,reverse(fld)) -1)

* Noel

Ray M SSC-Insane Points: 21093 More actions · Answer 2

Try using the replace function.

Update Mytable

set MyField = replace( MyField, '\\server1\folder1\1234', '\\server2\folder5\1234')

where MyField = '\\server1\folder1\1234'

Mark Johns Valued Member Points: 53 More actions · Answer 3

\\SERVER1\SCANFILES2\JPG SCANNING\PREVIEW TEST

Putting some thought into it its probably going to be easier just to update the server name to for example \\myserver and remove the scanfiles2 folder name so it would read

\\myserver\JPG SCANNING\PREVIEW TEST

Sorry for the confusion!

So what I have got so far is :-

select FilePath

from dbo.BulkImages

where filepath LIKE N'%JPG SCANNING%'

To select everything with JPG SCANNING in the UNC path but I cant work out how the replace function fits in!

Many Thanks

Mark.

Mark Johns Valued Member Points: 53 More actions · Answer 4

SORTED!!!!

Update dbo.BulkImages

set FilePath = replace( FilePath, '\\server1\scanfiles2', '\\NAS1')

where filepath LIKE N'%JPG SCANNING%

Thanks guys!!!!

Vladan SSC-Insane Points: 22072 More actions · Answer 5

Well, it depends on what precisely you want to do. If you just want to replace a string that is known, this is the solution:

select FilePath, REPLACE(FilePath,'SERVER1','MYSERVER') as NewFilePath

from dbo.BulkImages

where filepath LIKE N'%JPG SCANNING%'

But if there can be several different servers in the path, and you want to replace the server with 'MYSERVER' - no matter what it was originally - or to modify the path somehow, then you'll have to play around with it, locate the \ characters using CHARINDEX and then replace the corresponding part or create several strings and concatenate them. You could use this as a starting point:

select FilePath,

charindex('\',RIGHT(filepath,LEN(filepath)-2)) as pos, /*position of first \ after the starting double \*/

RIGHT (filepath, LEN(filepath)- charindex('\',RIGHT(filepath,LEN(filepath)-2))-2) as [path without server],

'\\MYSERVER\'+RIGHT (filepath, LEN(filepath)- charindex('\',RIGHT(filepath,LEN(filepath)-2))-2) as NewPath

from dbo.BulkImages

where filepath LIKE N'%JPG SCANNING%'

EDIT: Well, that's how it is when one has to work... I wrote the post, but then i got several urgent phone calls - and when I posted it, it was too late well, maybe next time!

Ray M SSC-Insane Points: 21093 More actions · Answer 6

You gotta be quick around here, Between Noeld, and Remi, its hard to get a post in.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 7

You had it easy on this one... I'm just monitoring it .

noeld SSC Guru Points: 96590 More actions · Answer 8

You are very swift too so shouldn't complain around here

* Noel

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 9

I hate to have to do this, again :

73,32,97,103,114,101,101

.

noeld SSC Guru Points: 96590 More actions · Answer 10

It would have been cooler if you use the Hex or maybe binary representation

* Noel

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 11

Ninja's_RGR'us

SSC Guru

Points: 294069

July 13, 2005 at 12:07 pm

#574077

Maybe but you still got the message .