Sql Server 2005 Image Field & Update Text
I have a field in a Sql Server 2005 database as type Picture. This field is actually storing a path to a pdf file on the server. I want to update the drive letter in the path that is stored in this image type field. Is this possible ? Is their a sql statement that can be used to update a portion of this field ?
Fieldname may say P:\Share\filename.pdf
Want to update the path to a different driveletter, but this field is of type image.
Fieldname may say P:\Share\filename.pdf
Want to update the path to a different driveletter, but this field is of type image.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Here's more specific information, I finally got the query needed to pull the results, below is the query
Select
CONVERT(nvarchar(max),(con
from invoice_image where image_id=1598
Below is the results
P:\2010\D-1 Recovery\124559.pdf
The file field is of image type. I would like to conver the drive letter in this field from P to R,
is their an update script that can be used to do this ?
Select
CONVERT(nvarchar(max),(con
from invoice_image where image_id=1598
Below is the results
P:\2010\D-1 Recovery\124559.pdf
The file field is of image type. I would like to conver the drive letter in this field from P to R,
is their an update script that can be used to do this ?
Check out the second of the two statements that I posted above.
combine that with the update:
update invoice_image
select ivoice_blob = convert(varbinary(max), 'R' + substring(CONVERT(nvarchar
where image_id=1598
But it's much easier to understand if you first pull out the path to a variable like I do, change it and then set the value. The rest is LFE.
combine that with the update:
update invoice_image
select ivoice_blob = convert(varbinary(max), 'R' + substring(CONVERT(nvarchar
where image_id=1598
But it's much easier to understand if you first pull out the path to a variable like I do, change it and then set the value. The rest is LFE.
ASKER
Thank you for the reply, I will try this script on Monday and will send a follow up to see how this works.
Thank you.
Thank you.
Here are another couple of ways you can do it:
UPDATE invoice_image
SET [file] = CAST('R' AS varbinary(1)) + SUBSTRING([file], 2, 100) -- Change 100 to the appropriate length
WHERE image_id = 1598
UPDATE invoice_image
SET [file] = STUFF(CAST([file] AS varbinary(100)), 1, 1, 'R') -- Change 100 to the appropriate length
WHERE image_id = 1598
You can first test out both methods as follows:
SELECT [file],
CAST('R' AS varbinary(1)) + SUBSTRING([file], 2, 100) Solution1,
STUFF(CAST([file] AS varbinary(100)), 1, 1, 'R') Solution2
FROM invoice_image
WHERE image_id = 1598
UPDATE invoice_image
SET [file] = CAST('R' AS varbinary(1)) + SUBSTRING([file], 2, 100) -- Change 100 to the appropriate length
WHERE image_id = 1598
UPDATE invoice_image
SET [file] = STUFF(CAST([file] AS varbinary(100)), 1, 1, 'R') -- Change 100 to the appropriate length
WHERE image_id = 1598
You can first test out both methods as follows:
SELECT [file],
CAST('R' AS varbinary(1)) + SUBSTRING([file], 2, 100) Solution1,
STUFF(CAST([file] AS varbinary(100)), 1, 1, 'R') Solution2
FROM invoice_image
WHERE image_id = 1598
ASKER
I would like to write an update statement to change the value of P to another letter. We need the users to be able to use a different drive letter that is free to more people so that more users can access these files through the application. You are correct the field type is image.
I know that a update statement starts something like the following
Update Tablename set fieldname='somevalue'
The table name is invoice_blob and the fieldname is path
Want to change the value of of fieldname ivoice_blob from "P" to "R"
the invoice_blob field is of image type though, didn't know if a conversion needed to be included
in the update statement.