Extracting Email addresses from a free text field

[evsy]

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]

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]

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]

nice use of the split function chris.

 

[stopher]

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]

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]

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