[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 257
  • Last Modified:

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.
0
Alvin Petitt
Asked:
Alvin Petitt
1 Solution
 
JestersGrindCommented:
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

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now