question

tlredd68 avatar image
tlredd68 asked

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
sql-server-2005sqlserver
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

tlredd68 avatar image tlredd68 commented ·
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.
0 Likes 0 ·
tlredd68 avatar image tlredd68 commented ·
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.
0 Likes 0 ·
Kevin Feasel avatar image
Kevin Feasel answered
This is very similar in concept to [an earlier question](http://ask.sqlservercentral.com/questions/33457/extracting-data-in-sql-2005), in that you can get the answer the same way: a [tally table](http://www.sqlservercentral.com/articles/T-SQL/62867/). 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.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

MCASumit avatar image
MCASumit answered
Use Send DB Mail Procedure and Return Email id ...............
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Matt Whitfield avatar image
Matt Whitfield answered
+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.
4 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Phil Factor avatar image Phil Factor commented ·
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#?
0 Likes 0 ·
Matt Whitfield avatar image Matt Whitfield ♦♦ commented ·
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 :)
0 Likes 0 ·
Phil Factor avatar image Phil Factor commented ·
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.
0 Likes 0 ·
Matt Whitfield avatar image Matt Whitfield ♦♦ commented ·
Yep, definitely in agreement there. Just presenting another option really :)
0 Likes 0 ·
Phil Factor avatar image
Phil Factor answered
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 */
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.