在Access之外的Jet/ACE查询中使用InStrRev()和类似功能 [英] using InStrRev() and similar functions in Jet/ACE queries outside of Access

问题描述

我，在建立一些SQL查询时我有点挣扎. 目前我正在使用:

Hia, I'm struggling a bit while forming some SQL queries. Currently I'm using:

InStrRev(TABLE.[_URL],"/")+1) AS OUTPUT

给出URL的输入-我想提取最后一部分，例如:

Given an input of a URL - I want to extract the last part eg:

www.url.com/some/text/here

应该返回

here

总是有3个斜杠，而我发布的摘录效果很好；只能在访问范围内.

There are always 3 slashes and while the snippet I posted works perfectly; only within access.

当我尝试在访问权限之外使用此功能时-我发现错误-对我可用的替代方法有何建议?

When I try and use this outside of access - I find errors - any advice on alternatives available to me?

推荐答案

正如您所发现的那样，从Access内部执行的SQL查询可以使用许多VBA函数(例如InStr()和InStrRev())，而这些函数本来就不被本地支持. SQL的Jet/ACE方言. Access对Jet/ACE SQL的扩展"最强大的方面之一是，您甚至可以编写自己的VBA函数，并在Access查询中使用 them .

As you have discovered, SQL queries executed from within Access can use many VBA functions (like InStr() and InStrRev()) that are not natively supported by the Jet/ACE dialect of SQL. One of the most powerful aspects of Access' "extensions" to Jet/ACE SQL is that you can even write your own VBA functions and use them in Access queries.

不幸的是，谈到普通" Jet/ACE SQL支持的功能时，事情可能会有些混乱，因为许多名称是相同的.如果我们可以在对在Access之外执行的Jet/ACE数据库的查询中使用Left()，Right()，Mid()等，那么为什么不使用InStr()和InstrRev()呢?不幸的是，答案是只是因为".

Unfortunately, things can get a bit confusing when it comes to what functions are supported in "plain" Jet/ACE SQL because many of the names are the same. If we can use Left(), Right(), Mid(), etc. in queries against Jet/ACE databases executed outside of Access, then why not InStr() and InstrRev()? The answer is, unfortunately, "Just because.".

在您的特殊情况下，我同意Remou的评论，您可能只需要检索查询中的整个列值，然后进行[c等效] InStrRev()相关的解析即可.

In your particular case I agree with Remou's comment that you'll probably just have to retrieve the entire column value in your query and then do the [equivalent of the] InStrRev()-related parsing afterward.

这篇关于在Access之外的Jet/ACE查询中使用InStrRev()和类似功能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！