x

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.

Thx
more ▼

asked Feb 22, 2011 at 04:58 PM in Default

tlredd68 gravatar image

tlredd68
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, 2011 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, 2011 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!
IF OBJECT_ID('dbo.Tally') IS NOT NULL 
    DROP TABLE dbo.Tally

SELECT TOP 11000 --equates to more than 30 years of dates
    IDENTITY(INT,1,1) AS N
INTO dbo.Tally
FROM Master.dbo.SysColumns sc1,
    Master.dbo.SysColumns sc2

ALTER TABLE dbo.Tally
ADD CONSTRAINT PK_Tally_N 
    PRIMARY KEY CLUSTERED (N) WITH FILLFACTOR = 100

GRANT SELECT, REFERENCES ON dbo.Tally TO PUBLIC

--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');


SELECT 
    SUBSTRING(' ' + p.String + ' ', N+1, CHARINDEX(' ', ' ' + p.String + ' ', N+1 ) - N-1)
FROM 
    dbo.Tally t
    cross join #CompanyInformation p
WHERE 
    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, 2011 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, 2011 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, 2011 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, 2011 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, 2011 at 08:38 AM Phil Factor
Yep, definitely in agreement there. Just presenting another option really :)
May 13, 2011 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],
                   embeddedEmail
            FROM   (SELECT 'The Imperial Oil Company Phil.Factor@ImpOil.com 123 Main St')
                   AS ListOfCompanies (EmbeddedEmail)
           )f
     )g
/*
EmailAddress
-------------------------
Phil.Factor@ImpOil.com
*/
more ▼

answered May 13, 2011 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, 2011 at 10:48 PM

MCASumit gravatar image

MCASumit
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

By RSS:

Answers

Answers and Comments

SQL Server Central

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

Topics:

x1945
x716
x84

asked: Feb 22, 2011 at 04:58 PM

Seen: 3067 times

Last Updated: Feb 22, 2011 at 04:58 PM