sqlsql-servert-sqlsubstringcharindex

Extracting Email from string in SQL Server


I have a column that contains an email address I am trying to extract.

It normally starts with "User:" and ends right before "TextClient". Tried to use a combination of SUBSTRING and CHARINDEX, returning the values after "User:". Another option was finding "@" and pull everything before and after. I have the following code so far but am pulling too much information.

WITH emailString AS
(
    SELECT 'Text Draft Saved - User: v-test123@blah.com TextClient: Blah' AS st
)
SELECT
    st
    , STUFF(SUBSTRING(st,1,CHARINDEX(' ',st,CHARINDEX('@', st))-1),1, 
    CHARINDEX('@', st) - CHARINDEX(' ',
    REVERSE(SUBSTRING(st,1,CHARINDEX('@', st)-1))),'')  
FROM emailString

I get the following:

st sub
Text Draft Saved - User: v-test123@blah.com TextClient: Blah Saved - User: v-test123@blah.com TextClient:

Solution

  • SELECT 
        SUBSTRING(
            'Text Draft Saved - User: v-test123@blah.com TextClient: Blah', 
            CHARINDEX('User: ', 'Text Draft Saved - User: v-test123@blah.com TextClient: Blah') + LEN('User: '), 
            CHARINDEX(' TextClient: ', 'Text Draft Saved - User: v-test123@blah.com TextClient: Blah') - (CHARINDEX('User: ', 'Text Draft Saved - User: v-test123@blah.com TextClient: Blah') + LEN('User: '))
        ) AS ExtractedEmail;
    

    or

    DECLARE @TestString NVARCHAR(100) = 'Text Draft Saved - User: v-test123@blah.com TextClient: Blah';
    
    SELECT 
        SUBSTRING(
            @TestString, 
            CHARINDEX('User: ', @TestString) + LEN('User: '), 
            CHARINDEX(' TextClient: ', @TestString) - (CHARINDEX('User: ', @TestString) + LEN('User: '))
        ) AS ExtractedEmail;
    

    Fiddler output