January 17, 2007 at 8:27 am
I want to change the following field in the database. I tried,
UPDATE heatgen
SET gdetail = REPLACE(convert(varchar(8000), gdetail), 'h:\', 'c:\')
Want it to be like this,
C:\ProgramFiles\HEAT\HEATSelfService\attachments\Winter019997.htm
want it to be H:\2007\Winter019997.htm
My aim is to move the files from C:\ to H:\ drive and update the database to reflect this. Thanks
January 17, 2007 at 8:46 am
Are all your files in the same folder on the C drive, or are they spread over more than one? If they're in the same one, you are on the right lines with your REPLACE syntax, although you have your H and your C the wrong way round. If your files are all in different folders, then please post back, as this will require slightly more complex logic.
John
January 17, 2007 at 8:49 am
Yes the files will always be in the same folder.
January 17, 2007 at 8:51 am
Then I'll leave the rest to you: look up the REPLACE syntax in Books Online. If there's any aspect of it that you don't understand, please let us know.
John
January 17, 2007 at 9:04 am
That is why I came here. I have not managed to find anything useful. If I use the above syntax it just adds H:\2007 to the path H:\2007Program Files - I cannot get it to change totally!!
January 17, 2007 at 9:13 am
That's because it's doing what you told it to: it's replacing every instance of "C:\" with "H:\2007". So what you need to do is include more than just "C:\" in your first argument - anything that you don't want to appear in the modified text should appear in the first argument.
John
January 17, 2007 at 9:25 am
Ok. I want this to update any path with any filename, so what could I use to keep this the same?
e.g. c:\program files...\test.htm
to
h:\2007\test.htm
* does not work!!
Thanks.
January 17, 2007 at 9:52 am
OK - that's why I asked at the beginning whether all the files were in the same place. It seems your problem is more complicated than it appeared. What you need to do is search the path for the last backslash, remove everything to the left of it, then append "H:\2007" at the beginning.
We use the CHARINDEX function to get the position of the final backslash. Unfortunately CHARINDEX returns the position of the first, so we must REVERSE the string:
declare
@STR varchar(1000)
set @STR = 'C:\ProgramFiles\HEAT\HEATSelfService\attachments\Winter019997.htm'
select reverse(@STR)
Now we look for the first backslash in the reversed string:
select
charindex('\', reverse(@STR))
Use the number we got to get rid of everything to the left:
select
right(@STR, charindex('\', reverse(@STR)))
Finally, add the new path on at the beginning:
select
'H:\2007' + right(@STR, charindex('\', reverse(@STR)))
Obviously, your code wouldn't be as convoluted as this - I've just done it step by step so you can see what I'm doing. You should be able to put it all into one UPDATE statement.
Good luck
John
January 18, 2007 at 3:07 am
Ok. Thanks for that excellent info John.
I tried, it hasn't updated! is it the correct format?
declare @STR varchar(1000)
UPDATE heatgen
set @STR = 'c:\Program Files\HEAT\HEATSelfService\attachments\Winter019997.htm'
select reverse(@str)
select charindex('\', reverse(@str))
select right(@str, charindex('\', reverse(@str)))
select 'H:\2007' + right(@str, charindex('\', reverse(@str)))
January 18, 2007 at 3:24 am
UPDATE heatgen
SET gdetail = 'H:\2007' + RIGHT(gdetail, CHARINDEX('\', REVERSE(gdetail)))
This is fairly basic stuff - I recommend that you spend some time learning the fundamentals of T-SQL, since you are likely to come across problems like this often.
John
January 18, 2007 at 4:21 am
I'm getting Line 9: Incorrect syntax near '='.
on this line;
SET gdetail = 'H:\2007' + RIGHT(gdetail, CHARINDEX('\', REVERSE(gdetail)))
January 18, 2007 at 5:11 am
Works for me. What data type is the gdetail column?
John
January 18, 2007 at 5:42 am
It's text!
January 18, 2007 at 6:04 am
The + operator doesn't work with text data types. I would suggest you change the column to varchar (surely you can't need over 4000 characters for a file path?) If you can't do that, try converting to varchar in your update statement, much like you did in your first post.
John
January 18, 2007 at 7:39 am
set gdetail = 'H:\2007' + RIGHT(convert(varchar(8000), gdetail), CHARINDEX('\', REVERSE(convert(varchar(8000), gdetail)))
I'll try this out first!
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply