从 MS Access 数据库表中的字段中删除字符 [英] removing characters from field in MS Access database table

问题描述

使用 MS Access 2010.我在表中有一个字段，其中包含用引号括起来的 Windows 路径名，如下所示

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

"C:My DocumentsPhotosimg1.jpg"
"C:My DocumentsPhotosproductsgizmo.jpg"
"C:My DocumentsPhotosimg5.jpg"

等等.

我需要去掉引号，所以列看起来像这样:

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

C:My DocumentsPhotosimg1.jpg
C:My DocumentsPhotosproductsgizmo.jpg
C:My DocumentsPhotosimg5.jpg

有没有办法编写更新查询来做到这一点?或者有更好的方法来完成它?

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

推荐答案

如果您将在 Access 会话中执行此操作，使用 Access 2000 或更高版本，您可以使用 Replace() 函数在更新查询中删除引号.删除意味着用空字符串替换它们.

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, '"', '');

如果这些路径字符串中的任何一个可以在其中包含引号 (糟糕！)，请考虑 Mid() 函数...要求它从第二个字符开始(跳过前导引号)，并返回相当于 Len(path_field) - 2

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);

无论哪种方式，您都可能希望包含一个 WHERE 子句来忽略没有 path_field 值的行.

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

WHERE Len(path_field) > 0

如果在添加新数据时必须再次执行此操作，请使用不同的 WHERE 子句以确保仅更新 path_field 值以引号开头和结尾的行.

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 '"*"'

那是使用 * 通配符作为 Access 的默认 ANSI 89 模式.如果您要从 ADO(ANSI 92 模式)执行此操作，请使用 % 通配符.

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 '"%"'

... 或使用 Alike 和 % 通配符与任一模式.

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

WHERE path_field ALike '"%"'

这篇关于从 MS Access 数据库表中的字段中删除字符的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！