Extract email from string

Ex: ABC Company jr68@gmail.com 123 Main St

Every record has a space before and after the email address. I just want jr68@gmail.com returned using a select.

more ▼

asked Feb 22 '11 at 04:58 PM in Default

tlredd68 gravatar image

111 7 8 8

Thx! I will use this and learn how to use the Tally Table. It appears I will have a lot of these issues in the future so I must learn this concept. I will give it a shot.
Feb 22 '11 at 06:00 PM tlredd68
This worked like a charm. The other responses are good as well but we do not have access to C#. We are strictly a MS SQL 2005 shop; so, all of my solutions must be in SQL which this is. I will now start using the Tally table for a lot of this string manipulation. Thanks Kevin and Jeff Moden.
Feb 23 '11 at 06:26 AM tlredd68
(comments are locked)
10|1200 characters needed characters left

4 answers: sort voted first

This is very similar in concept to an earlier question, in that you can get the answer the same way: a tally table. These things are pretty powerful and the performance is great. :-)

Using the tally table, the answer from before--where you asked for getting a subsection of the data--is almost exactly the same as the one here:

--Create a tally table.  All Hail Jeff Moden!
    DROP TABLE dbo.Tally

SELECT TOP 11000 --equates to more than 30 years of dates
INTO dbo.Tally
FROM Master.dbo.SysColumns sc1,
    Master.dbo.SysColumns sc2



--Now use the tally table to extract the relevant information.
create table #CompanyInformation
    Id int,
    String varchar(1000)

insert into #CompanyInformation values (1, 'ABC Company jr68@gmail.com 123 Main St');
insert into #CompanyInformation values (2, 'DEF Company jr68222@gmail.com 123 Main St Floor 4');

    SUBSTRING(' ' + p.String + ' ', N+1, CHARINDEX(' ', ' ' + p.String + ' ', N+1 ) - N-1)
    dbo.Tally t
    cross join #CompanyInformation p
    N < LEN(' ' + p.String + ' ')
    AND SUBSTRING(' ' + p.String + ' ', N, 1) = ' '
    AND SUBSTRING(' ' + p.String + ' ', N+1, CHARINDEX(' ', ' ' + p.String + ' ', N+1 ) - N-1) LIKE '%@%.%'

drop table #CompanyInformation;
The only difference from my answer to that earlier post is adding in one more where clause, where we're looking for an @ sign followed eventually by a dot (which you're unlikely to find in non-email field). That's one of the many awesome things about the tally table: it helps you solve an entire class of pattern-search problems, just like the ones you're running into now.
more ▼

answered Feb 22 '11 at 05:33 PM

Kevin Feasel gravatar image

Kevin Feasel
6.1k 3 5 11

(comments are locked)
10|1200 characters needed characters left
+1 to Kevin, but another option for this would be to use a CLR Scalar function - write the code in C#, get it to return the result. They have a much lower performance overhead than T-SQL Scalar Functions, so can yield very positive results, especially in this sort of area (string manipulation) where SQL Server has traditionally been weak.
more ▼

answered Feb 22 '11 at 11:58 PM

Matt Whitfield gravatar image

Matt Whitfield ♦♦
29.4k 61 65 87

Come off it Matt! OK, the routine I've given as an answer will process 100,000 rows in 7 seconds (Yup. I test 'em!). Surely that's fast enough for most purposes. Why then bother with all the overhead of C#?
May 13 '11 at 07:01 AM Phil Factor
Well, to me, it's not really an overhead. I would be able to write, test & deploy a CLR function to do that quicker than the SQL Server one, which would then be re-usable when moving it to the BLL, which is where it should be... And I don't think that the CLR function would be quicker than an in-line SQL solution - but it would certainly be faster than a SQL Scalar Function... If I didn't think writing it in SQL was a viable method, I wouldn't have +1'd Kevin's answer :)
May 13 '11 at 07:13 AM Matt Whitfield ♦♦
Agreed, a scalar TSQL function won't perform with industrial quantities of data. In-line SQL always looks awkward but it can really fly. I hope I'm not anti-CLR, but I wouldn't want to encourage people to use CLR instead of SQL purely because they think it will always outperform SQL. There are so many other issues that will influence that decision in commercial IT departments.
May 13 '11 at 08:38 AM Phil Factor
Yep, definitely in agreement there. Just presenting another option really :)
May 13 '11 at 12:21 PM Matt Whitfield ♦♦
(comments are locked)
10|1200 characters needed characters left
SELECT  CASE WHEN AtIndex=0 THEN '' --no email found 
           ELSE RIGHT(head, PATINDEX('% %', REVERSE(head) + ' ') - 1) 
        + LEFT(tail + ' ', PATINDEX('% %', tail + ' ')) 
        END EmailAddress
FROM (SELECT RIGHT(EmbeddedEmail, [len] - AtIndex) AS tail,
             LEFT(EmbeddedEmail, AtIndex) AS head, AtIndex
      FROM (SELECT PATINDEX('%[A-Z0-9]@[A-Z0-9]%', EmbeddedEmail+' ') AS AtIndex,
                   LEN(EmbeddedEmail+'|')-1 AS [len],
            FROM   (SELECT 'The Imperial Oil Company Phil.Factor@ImpOil.com 123 Main St')
                   AS ListOfCompanies (EmbeddedEmail)
more ▼

answered May 13 '11 at 06:58 AM

Phil Factor gravatar image

Phil Factor
3.8k 8 9 16

(comments are locked)
10|1200 characters needed characters left
Use Send DB Mail Procedure and Return Email id ...............
more ▼

answered Feb 22 '11 at 10:48 PM

MCASumit gravatar image

63 2 3 3

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Feb 22 '11 at 04:58 PM

Seen: 2726 times

Last Updated: Feb 22 '11 at 04:58 PM