从查询中选择单词的一部分?

编程入门行业动态更新时间:2024-10-28 20:25:32

本文介绍了从查询中选择单词的一部分?的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！问题描述

在 Access 中，我有一个这样的字段:

In Access, I have a field like this:

从 01/05/2013 开始到结束，使用了 XXXXXXX 设备.

From the start until end at 01/05/2013, the XXXXXXX device was used.

我正在寻找可以提取设备名称(在本例中为 XXXXXXX)的查询.我能够使用 mid() 让它有点工作，但句子可能更长或更短.还有哪些其他方法可以做到这一点?是否可以使用 find() 来查找设备"的位置?

I'm looking for a query that can extract the device name (in this case XXXXXXX). I was able to use mid() to get it somewhat working, but the sentence could be longer or shorter. What are some other ways to do this? Is there a find() I can use to find the location of "device"?

我的 SQL 看起来像:

My SQL looks like:

SELECT Mid(Note, 68, 30) FROM My_Log WHERE Note LIKE "*, the*" ;

推荐答案

如果设备名称是device"之前的单词，并且您想仅使用 Access db 引擎直接支持的函数来查找该单词，Mid()、InStr() 和 InstrRev() 可以完成工作......但它不会很漂亮.

If the device name is the word before "device", and you want to find that word using only functions supported directly by the Access db engine, Mid(), InStr(), and InstrRev() can get the job done ... but it won't be pretty.

这是一个即时窗口会话...

Here is an Immediate window session ...

Note = "From the start until end at 01/05/2013, the XXXXXXX device was used." ? Mid(Note, _ InstrRev(Note, " ", InStr(1, Note, " device") -1) +1, _ InStr(1, Note, " device") - InstrRev(Note, " ", InStr(1, Note, " device") -1) -1) XXXXXXX

因此您需要在查询中使用复杂的 Mid() 表达式.

So you would then need to use that complex Mid() expression in a query.

但是，如果您可以在查询中使用用户定义的函数，则逻辑会更易于管理.

However, if you can use a user-defined function in your query, the logic could be easier to manage.

Public Function FindDevice(ByVal pInput As String) As Variant Dim astrWords() As String Dim i As Long Dim lngUBound As Long Dim varReturn As Variant varReturn = Null astrWords = Split(pInput, " ") lngUBound = UBound(astrWords) For i = 0 To lngUBound If astrWords(i) = "device" Then varReturn = astrWords(i - 1) Exit For End If Next i FindDevice = varReturn End Function