removing characters from field in MS Access database table

Question

Using MS Access 2010. I have a field in a table that contains windows path names surrounded by quotes, like this

"C:\My Documents\Photos\img1.jpg"
"C:\My Documents\Photos\products\gizmo.jpg"
"C:\My Documents\Photos\img5.jpg"

and so on.

I need to get rid of the quotes so the column looks like this:

C:\My Documents\Photos\img1.jpg
C:\My Documents\Photos\products\gizmo.jpg
C:\My Documents\Photos\img5.jpg

Is there a way to write an update query to do this? OR a better way to do it altogether?

Answer 1

If you will be doing this from within an Access session, using Access 2000 or later, you can use the Replace() function in an update query to remove the quotes. Remove would mean replace them with an empty string.

UPDATE YourTable
SET path_field = Replace(path_field, '"', '');

If any of those path strings could include quotes within them (yuck!), consider the Mid() function ... ask it to start at the 2nd character (skipping the lead quote), and return the number of characters equivalent to Len(path_field) - 2

UPDATE YourTable
SET path_field = Mid(path_field, 2, Len(path_field) - 2);

Either way, you may want to include a WHERE clause to ignore rows without path_field values.

WHERE Len(path_field) > 0

And if you must do this again when new data is added, use a different WHERE clause to ensure you UPDATE only those rows whose path_field values start and end with quotes.

WHERE path_field Like '"*"'

That was using the * wild card for Access' default ANSI 89 mode. If you will do this from ADO (ANSI 92 mode), use the % wild card.

WHERE path_field Like '"%"'

... or use ALike and the % wild card with either mode.

WHERE path_field ALike '"%"'