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
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.
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.
Use Send DB Mail Procedure and Return Email id ...............
+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.
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#?
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 :)
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.
Yep, definitely in agreement there. Just presenting another option really :)
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 */
