update part of a string - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

update part of a string

Author

Topic

Cedtech23
Starting Member

4 Posts

Posted - 2006-12-06 : 15:34:36

I want to update part of a string stored in a column of a database

Example:

Currently the column contains a string with the complete path to an image I want to change part of the string that say Serv1 to Serv2

the image name is different for every image but the path is the same

How can I accomplish this??/


UPDATE tblImages
SET ImagePath = \\Serv2\imgs\img10.gif

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-12-06 : 15:49:06

Here's one way:


--make sure it looks right first:
select ImagePath
	,replace(ImagePath, '\\Serv1\', '\\Serv2\') as [newImagePath]
from	tblImages
where ImagePath like '\\Serv1\%'

UPDATE tblImages SET 
    ImagePath = replace(ImagePath, '\\Serv1\', '\\Serv2\')
where ImagePath like '\\Serv1\%'

Be One with the Optimizer
TG

Cedtech23
Starting Member

4 Posts

Posted - 2006-12-06 : 15:53:12

So I have 500 records in that column

the first argument for the REPLACE is ImagePath how will the function know the string of the path each record? or do I have to do something else as well, each image name is different?

Example
\\Serv2\imgs\img10.gif
\\Serv2\imgs\img12.gif
\\Serv2\imgs\img23.gif

\\Serv2\imgs\img14.gif

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-12-06 : 16:12:04

Just run the first (select) statement to see how it works. Also look at REPLACE in Books Online.

Be One with the Optimizer
TG

Cedtech23
Starting Member

4 Posts

Posted - 2006-12-06 : 16:21:11

Thank You

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources