How to search for specific Text in a field in Access and return what is between that?

[robertadamharper]

Hi

I have a list of text values within a field in access and I want to
return the text that appears to the left of that it the field value
matches specific text?

eg.
Value
"www.google.co.uk/search?hl=en&q=hazard signs&meta="

if the value matches "search?hl=en&q" return what is to the left of
that up until the "&meta="

Answer
Would return "=hazard signs"

Many Thanks

 

[Douglas J. Steele]

I'm guessing you mean 'what is to the right of that up until the "&meta="'.
In other words, that for what you showed, you want "=hazard signs" to be
returned. On that assumption, something like the following untested aircode
should work:

Dim lngAmpersand As Long
Dim lngSearchFound As Long
Dim strDesiredString As String
Dim strToFind As String
Dim strToSearch As String

strToSearch = "www.google.co.uk/search?hl=en&q=hazard signs&meta="
strToFind = "search?hl=en&q"

lngSearchFound = InStr(strToSearch, strToFind)
' lngSearchFound will be 0 if the phrase isn't found
If lngSearchFound > 0 Then
' Reset lngSearchFound so that it points to the location
' after "search?hl=en&q"
lngSearchFound = lngSearchFound + Len(strToFind)
strDesiredString = Mid(strToSearch, lngSearchFound)
' If there's an ampersand in strDesiredString, only get
' what's in front of it
lngAmpersand = InStr(strDesiredString, "&")
If lngAmpersand > 0 Then
strDesiredString = Left(strDesiredString, ) _
Len(strDesiredString) - lngAmpersand))
End If
End If

 

[coasterman via AccessMonster.com]

Two quicker/shorter offerings using regular expressions on the previous text
example:

1. To do what you asked for - eg, text to the left of a string in a field etc.


Public Function LeftOfX(strX As String, strFind As String) As String
Dim re As Object
Set re = CreateObject("VBscript.RegExp")
re.Pattern = "(.*)(" & strFind & ")(.*)"
LeftOfX = re.Replace(strX, "$1")
End Function

where using it with the same source text and looking for 'signs&meta='
? LeftOfX("www.google.co.uk/search?hl=en&q=hazard signs&meta="),"signs&meta=")
returns

"www.google.co.uk/search?hl=en&q=hazard "

2. Reproducing the previous answer of getting text between two known strings


Public Function BetweenX(strX As String) As String
Dim re As Object
Set re = CreateObject("VBscript.RegExp")
re.Pattern = "(.*)(search\?hl=en\&q)(.+)(\&meta=)(.*)"
BetweenX = re.Replace(strX, "$3")
End Function

? BetweenX("www.google.co.uk/search?hl=en&q=hazard signs&meta=") returns

"=hazard signs"

Note that I haven't put any code in to trap Regular Expression metacharacters
(see below) in any of the strings and 'escape' them using a '\'.

\ back-slash (reverse solidus)
/ forward slash (solidus)
^ caret
$ dollar
* asterisk
+ plus
? question mark
{ left brace
} right brace
[ left bracket
] right bracket
( left parenthesis
) right parenthesis
.. period (full stop)