从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 Documents\Photos\img1.jpg"
"C:\My Documents\Photos\products\gizmo.jpg"
"C:\My Documents\Photos\img5.jpg"

以此类推.

我需要删除引号，以便该列看起来像这样:

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?

推荐答案

如果要使用Access 2000或更高版本在Access会话中执行此操作，则可以在更新查询中使用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屋！