Replace Table Values in SQL Server 2000

I need to replace part of a table value with another value. Example is that the field is a file location. It's current value is "Z:\Program Files\DataFile\thisisafile.txt". I need the new value to be "Z:\DataFile\thisisafile.txt".

In other words, only replacing the first part of the file location, keeping the file name the same.

I hope that makes sense. It's easier to think of than to describe. Thanks in advance for any help.
Alvin PetittAsked:
Who is Participating?
 
JestersGrindConnect With a Mentor Commented:
Are you replacing that text in all columns or just some?  You can use this.

UPDATE YourTable SET YourColumn = REPLACE(YourColumn, '\Program Files', '')

If you run that as is, it will replace the \Program Files string in every row where it exists.  If you only want to affect a few records, you need to apply a WHERE clause.

Greg

0
 
Alvin PetittAuthor Commented:
Thanks, Greg. I forgot to mention that I want to change all rows. There are about 442,000 rows where file locations exist. I need to replace part of the file location as the path has changed, in all rows. I'm an SQL noob, just saying.
0
 
BrandonGalderisiCommented:
If the data will always be at the beginning:

DECLARE @a TABLE (a varchar(255))
INSERT INTO @a(a) VALUES('Z:\Program Files\DataFile\thisisafile.txt')

--26 represents the length of the string you want to replace ('Z:\Program Files\DataFile\')
--255 is the length of the field
SELECT a,'Z:\DataFile\'SUBSTRING(a,26,255)
FROM @a


If it will not:
DECLARE @a TABLE (a varchar(255))
INSERT INTO @a(a) VALUES('Z:\Program Files\DataFile\thisisafile.txt')

--26 represents the length of the string you want to replace ('Z:\Program Files\DataFile\')
--255 is the length of the field
SELECT a,REPLACE(a,'Z:\Program Files\DataFile\','Z:\DataFile\')
FROM @a
WHERE a LIKE '%Z:\Program Files\DataFile\%'



0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.