I need to extract an email address from the free text field which will have other values in it.
IE the field may contains:
MY email address has changed to dave@bmi.com thanks
I want to write a query to select dave@bmi.com
I guess it involves mid and instr functions but am struggling to get anywhere
Help very much appreciated
Dave
ajetrumpet
11-20-2009, 01:17 AM
I need to extract an email address from the free text field which will have other values in it.
IE the field may contains:
MY email address has changed to dave@bmi.com thanks
I want to write a query to select dave@bmi.com
I guess it involves mid and instr functions but am struggling to get anywhere
Help very much appreciated
Dave
this is not easy dave, but i would suspect that this would be it, if not very close:SELECT MID(
TBL.FLD,
instrrev(
TBL.FLD," ",instr(TBL.FLD,"@")
) + 1,
instr(
instrrev(
TBL.TEXT," ",instr(TBL.FLD,"@")
) + 1, TBL.FLD, " "
) -
instrrev(TBL.FLD," ",
instr(TBL.FLD,"@")))
FROM TBL;
stopher
11-20-2009, 03:10 AM
This isn't strictly an answer as you asked for a query solution. However, if you get stuck with Adam's solution, try this function:
Public Function GetEmail(strText As String) As String
Dim vrtWord As Variant
For Each vrtWord In Split(strText, " ")
If InStr(1, vrtWord, "@") > 1 And InStr(1, vrtWord, "@") < Len(vrtWord) Then
GetEmail = CStr(vrtWord)
End If
Next
End Function
hth
Chris
ajetrumpet
11-20-2009, 03:55 AM
nice use of the split function chris.
stopher
11-20-2009, 11:55 PM
nice use of the split function chris.
Cheers Adam. It's a tip I picked up in the forums from another AWFVIP (can't remember who).
Chris
Mike375
11-21-2009, 02:08 AM
This isn't strictly an answer as you asked for a query solution. However, if you get stuck with Adam's solution, try this function:
Chris
Chris,
Why isn't that a query solution? Works for me
exp: GetEmail([abc])
stopher
11-21-2009, 05:34 AM
Chris,
Why isn't that a query solution? Works for me
exp: GetEmail([abc])
Because it requires you to write some VBA whereas Adam's solution is written wholly within SQL. Some folks prefer a non-VBA solution as they have not ventured into VBA. But yes, the function can be called from within SQL.
Chris