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

avatar image

tlredd68
111 7 8 10

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

avatar image

Kevin Feasel
6.2k 4 7 15

(comments are locked)
10|1200 characters needed characters left
  • 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

avatar image

Matt Whitfield ♦♦
29.5k 62 66 88

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

avatar image

Phil Factor
4.2k 8 22 20

(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

avatar image

MCASumit
63 3 3 4

(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.

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:

x2016
x985
x95

asked: Feb 22, 2011 at 04:58 PM

Seen: 4752 times

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

Copyright 2016 Redgate Software. Privacy Policy