?
Solved

How to extract an email address from a description via a query in MS Access

Posted on 2015-02-12
5
Medium Priority
?
135 Views
Last Modified: 2015-02-15
Hello

Does anyone know how to extract an email address from a field via a query?

I have a list of descriptions and some include an email address somewhere in the text and I am trying to extract just the email address, as I don't do code I would like to be able to do this in a query if possible, can anyone help?

Thanks
0
Comment
Question by:LillyC
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 48

Expert Comment

by:Dale Fye
ID: 40605874
While you could do this in a query, it would be easier to create a function and pass the text field to the function.  Something like:

Public Function fnEmailFromText(SomeValue As Variant) As Variant

    'Pass and return Variant to allow for NULL fields and to return a NULL value
    'if a valid email syntax is not found.
    'This function will only extract the first string that looks like a valid email string

    Dim intAtPosn As Integer, intStart As Integer, intEnd As Integer
    Dim strEmail As String

    If IsNull(SomeValue) Then
        fnEmailFromText = Null
        Exit Function
    ElseIf InStr(SomeValue, "@") = 0 Then
        fnEmailFromText = Null
        Exit Function
    End If
    
    intStart = 1
    intAtPosn = InStr(intStart, SomeValue, "@")
    Do While intAtPosn <> 0
    
        intStart = InStrRev(" " & SomeValue, " ", intAtPosn)
        intEnd = InStr(intAtPosn, SomeValue & " ", " ")
        
        strEmail = Mid(SomeValue, intStart, intEnd - intStart)
        If strEmail Like "*[@]*[.]*" Then
            fnEmailFromText = strEmail
            Exit Function
        End If
        intAtPosn = InStr(intAtPosn + 1, SomeValue, "@")

    Loop
    
End Function

Open in new window

Then, in your query you would use something like:

SELECT fnEmailFromText([SomeField]) as Email
FROM yourTable
WHERE fnEmailFromText IS NOT NULL
0
 
LVL 29

Accepted Solution

by:
IrogSinta earned 2000 total points
ID: 40605961
Here's a way via query.  I broke it down into 6 calculated fields for clarity but its up to you if you want to combine them all into one.  The last field returns the email address and includes the LIKE operator to prevent a function error if no email address exists.

SELECT Field1, InStr([Field1],"@") AS [pos@], InStr([pos@],[Field1],".") AS posDot, InStr([posDot],[Field1]," ") AS posSpc, InStrRev([Field1]," ",[Pos@])+1 AS beg, IIf([posSpc]=0,Len([Field1])+1,[posSpc]) AS [end], IIf([Field1] Not Like "*@*.*","",Mid([Field1],[beg],[end]-[beg])) AS eMail
FROM TableName;

Open in new window

sqlRon
0
 

Author Closing Comment

by:LillyC
ID: 40609905
Absolutely brilliant, thank you
0
 
LVL 48

Expert Comment

by:Dale Fye
ID: 40610124
The function method is so much easier, and you can add documentation to explain what you are trying to do.
0
 

Author Comment

by:LillyC
ID: 40611088
Thank you Dale, the function method may be much easier but I'm not familiar with functions so I'm afraid it wouldn't be easier for me, but thank you anyway.
0

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Suggested Courses

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question