从查询中选择单词的一部分? [英] Select part of word from query?

问题描述

在 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

那么查询可能是......

Then the query could be ...

SELECT FindDevice(Note) AS device_name
FROM My_Log
WHERE Note LIKE "*, the*"

这篇关于从查询中选择单词的一部分?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！