Extract Email Address from String Using Tsql

Extract email address from string using tsql

You can search for '@' in the string. Then you get the string at the LEFT and RIGHT side of '@'. You then want to REVERSE the LEFT side and get first occurrence of ' ' then get the SUBSTRING from there. Then REVERSE it to get the original form. Same principle apply to the RIGHT side without doing REVERSE.

Example string: 'some text someemail@domain.org some text'

1. LEFT = 'some text someemail'
2. RIGHT = '@domain.org some text'
3. Reverse LEFT = 'liameemos txet emos'
4. SUBSTRING up to the first space = 'liameemos'
5. REVERSE(4) = someemail
6. SUBSTRING (2) up to the first space = '@domain.org'
7. Combine 5 and 6 = 'someemail@domain.org'

Your query would be:

;WITH CteEmail(email) AS(
    SELECT 'someemail@domain.org' UNION ALL
    SELECT 'some text someemail@domain.org some text' UNION ALL
    SELECT 'no email'
)
,CteStrings AS(
    SELECT
        [Left] = LEFT(email, CHARINDEX('@', email, 0) - 1),
        Reverse_Left = REVERSE(LEFT(email, CHARINDEX('@', email, 0) - 1)),
        [Right] = RIGHT(email, CHARINDEX('@', email, 0) + 1)
    FROM CteEmail
    WHERE email LIKE '%@%'
)
SELECT *,
    REVERSE(
        SUBSTRING(Reverse_Left, 0, 
            CASE
                WHEN CHARINDEX(' ', Reverse_Left, 0) = 0 THEN LEN(Reverse_Left) + 1
                ELSE CHARINDEX(' ', Reverse_Left, 0)
            END
        )
    )
    +
    SUBSTRING([Right], 0,
        CASE
            WHEN CHARINDEX(' ', [Right], 0) = 0 THEN LEN([Right]) + 1
            ELSE CHARINDEX(' ', [Right], 0)
        END
    )
FROM CteStrings

Sample Data:

email
----------------------------------------
someemail@domain.org
some text someemail@domain.org some text
no email

Result

---------------------
someemail@domain.org
someemail@domain.org

How can I extract string 'foo' from a string foo@mail.com?

You're inadvertently re-implementing "right" there :)

Try this:

select left(emailaddress, charindex('@', emailaddress) - 1)

It seeks the position of the @, and takes the number of characters up to but not including (that's the - 1) the @ symbol.

Be careful with strings that don't have an @ though; you'll end up an empty string.

Extract Emails that Lying Between Special Characters Using Regex in SQL

With the use of a helper function

Example or dbFiddle

Declare @YourTable Table (LogID int,[Logmessage] varchar(500))  Insert Into @YourTable Values 
 (1,'Alan Robert <alan.robert@gmail.com> was assigned to <richard@yahoo.com> and <nelson@gmail.com>')
,(2,'Alan Robert <alan.robert@gmail.com> was unassigned to <khanjoyty@gmail.com> and <katy@gmail.com>')

Select A.LogID
      ,B.* 
 From @YourTable A
 Cross Apply [dbo].[tvf-Str-Extract-JSON](LogMessage,'<','>') B

Results

LogID   RetSeq  RetVal
1       1       alan.robert@gmail.com
1       2       richard@yahoo.com
1       3       nelson@gmail.com
2       1       alan.robert@gmail.com
2       2       khanjoyty@gmail.com
2       3       katy@gmail.com

It would then be a small matter to pivot the results

The TVF if interested

CREATE FUNCTION [dbo].[tvf-Str-Extract-JSON] (@String varchar(max),@Delim1 varchar(100),@Delim2 varchar(100))
Returns Table 
As
Return (  

    Select RetSeq = row_number() over (order by RetSeq)
          ,RetVal = left(RetVal,charindex(@Delim2,RetVal)-1)
    From  (
            Select RetSeq = [Key]+1
                  ,RetVal = trim(Value)
             From  OpenJSON( '["'+replace(string_escape(@String,'json'),@Delim1,'","')+'"]' )

          ) C1
    Where charindex(@Delim2,RetVal)>1

)

---

Related Topics