Replace a filepath column in SQL with new file structure

Question

I have recently changed the structure of a drive where I keep a lot of my 'estimating' files.

The problem is, I use a program that has references to some of those files, in the old structure, stored in a SQL database.

I would like to be able to use a SQL query to Replace the start of the string in the "ImagePath" column with the new file path. Or if someone can suggest a better way of doing this!?

The old structure was:

X:\Estimating Dept\Estimating Files\Estimates\E28000 - E28999\E28600 - E28699\E28654\...

I need the new filepath to be:

S:\E28xxx\6xx\54\...

I have tried the following SQL command:

UPDATE BidPages SET ImagePath = REPLACE(ImagePath,"X:\Estimating Dept\Estimating Files\Estimates\E28000 - E28999\E28600 - E26899\E28654","S:\Estimates\E28xxx\6xx\54\")

in Microsoft SQL Server Management Studio 2008, but I get the error:

Msg 207, Level 16, State 1, Line 4 Invalid column name 'X:\Estimating Dept\Estimating Files\Estimates\E28000 - E28999\E28600 - E28699\E28654'. Msg 207, Level 16, State 1, Line 4 Invalid column name 'S:\Estimates\E28xxx\6xx\54\'.

The table name I want to update is dbo.BidPages and the column name is ImagePath.

Also, is there a way to increment the digits in the filepath based on the original filepath?

Answer 1

I just realised I need to use single quotes, as opposed to double quotes.