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
FROM Master.dbo.SysColumns sc1,
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
insert into #CompanyInformation values (1, 'ABC Company firstname.lastname@example.org 123 Main St');
insert into #CompanyInformation values (2, 'DEF Company email@example.com 123 Main St Floor 4');
SUBSTRING(' ' + p.String + ' ', N+1, CHARINDEX(' ', ' ' + p.String + ' ', N+1 ) - N-1)
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.
Feb 22, 2011 at 05:33 PM