Anyone know about Parsing an email?

Hello everyone

Heres what it looks like:

I have a large file of over 40k email records. The emails are all mixed up and come in various formats but i noticed that most of them are in this format:

firstname.lastname@.email.com
firstname.middlename.lastname@.email.com

For all those emails with the period (.) in between, the (.) actually separates an individuals first and last name.

My task is this, to separate all the emails that are in this format into first and last name fields. I'm stimped folks and I'll really appreciate any pointers or ideas on how to go about solving this task.

Thanksuse substring and charindexsql

Any Gurus...?

I asked the following question in the 'MS Access' boards but I am wondering if there is a way to do it on our SQL server as well? Heres the original question:

"Dont ask why, we just need to do something odd...How can you use the system tables in access to do the following:

1. write a query on the fly for each table in the database (without knowing the table names ahead of time)

2. the query also needs to combine all the fields in a table into one single field (ie - queryfield:[field1]&[field2}&[etc...]), again without knowing what the field names are ahead of time

I have searched news groups and web rings all night. Please help!"You'll get the tables by searching the table Sysobjects for all objects whose xtype is "U". Write a SP that does a cursor loop through all records satisfying that search and, for each match, runs that query that you want to run on all tables.

To concatenate all fields, I guess you'll need to first investigate the columns' data types and convert non-string types to strings? And handle null values? And handles non-convertable types?
Start with the Syscolumns column...

Check first if there are any system SP's that could help you avoid having the code Select's directly against system tables.|||I asked the following question in the 'MS Access' boards but

Q1 I am wondering if there is a way to do it on our SQL server as well?

Heres the original question: "Dont ask why, we just need to do something odd...How can you use the system tables in access to do the following: 1. write a query on the fly for each table in the database (without knowing the table names ahead of time) 2. the query also needs to combine all the fields in a table into one single field (ie - queryfield:[field1]&[field2}&[etc...]), again without knowing what the field names are ahead of time I have searched news groups and web rings all night. Please help!"

A1 Yes, it is certainly possible (in Sql Server and in MS Access). The original #2. may entail work arounds (with many very long table names, especially if these include spaces and / or special characters)

The applicable special stored procedures (Sql Server 2000 sp_) would be sp_tables and sp_columns. One may also make use of [INFORMATION_SCHEMA] views to address your tasks.

-- Example useages of sp_tables and sp_columns:

Use Pubs
Go

Exec sp_tables
Exec sp_tables @.table_type = ['Table']
Exec sp_tables @.table_type = ['View']
Exec sp_tables @.table_type = ['System Table']

Exec sp_columns @.table_name = 'Authors'
--------

-- General List: Catalog Special Stored Procedures:
sp_column_privileges
sp_special_columns
sp_columns
sp_sproc_columns
sp_databases
sp_statistics
sp_fkeys
sp_stored_procedures
sp_pkeys
sp_table_privileges
sp_server_info
sp_tables
--------

-- INFORMATION_SCHEMA views (return metadata of DB objects):

CHECK_CONSTRAINTS
COLUMN_DOMAIN_USAGE
COLUMN_PRIVILEGES
COLUMNS
CONSTRAINT_COLUMN_USAGE
CONSTRAINT_TABLE_USAGE
DOMAIN_CONSTRAINTS
DOMAINS
KEY_COLUMN_USAGE
PARAMETERS
REFERENTIAL_CONSTRAINTS
ROUTINES
ROUTINE_COLUMNS
SCHEMATA
TABLE_CONSTRAINTS
TABLE_PRIVILEGES
TABLES
VIEW_COLUMN_USAGE
VIEW_TABLE_USAGE
VIEWS

----
Note: Selecting data from information schema views requires using an [INFORMATION_SCHEMA] qualified object name i.e.(in the position where one normally specifies nothing, or the dboo name as appropriate). For example:

SELECT *
FROM Master.[INFORMATION_SCHEMA].COLUMNS
----

-- THE FOLLOWING would fail (unless someone has created a user table / view named 'COLUMNS'):

SELECT *
FROM Master..COLUMNS

SELECT *
FROM Master.dbo.COLUMNS
--------|||Just an example... Improvement and tidying could be made I'm sure!

declare @.id int,
@.name varchar(255),
@.transaction varchar(8000),
@.queryField varchar(8000),
@.col_list varchar(8000),
@.tab_name varchar(255),
@.col_err real,
@.tab_err real

declare table_scan cursor for
select name, id
from sysobjects
where type = 'U'

open table_scan

fetch table_scan into @.tab_name, @.id
set @.tab_err = @.@.FETCH_STATUS
PRINT @.tab_err

while (@.tab_err = 0 )
begin
DECLARE column_scan CURSOR FOR
SELECT name
FROM syscolumns
WHERE ID = @.id

OPEN column_scan
FETCH column_scan into @.name

SELECT @.col_list = @.name
SELECT @.queryField = 'queryfield:['+@.name+']'

SELECT @.col_err = @.@.FETCH_STATUS

WHILE (@.col_err = 0)
begin
SELECT @.col_list = @.col_list +', ' + @.name
SELECT @.queryField = @.queryField + '&['+@.name+']'

FETCH column_scan into @.name
set @.col_err = @.@.FETCH_STATUS
end
SELECT @.transaction = 'SELECT '+ @.col_list + ' FROM ' + @.tab_name
PRINT '================================================= '
PRINT @.transaction
print @.QueryField
PRINT '================================================= '
EXEC(@.transaction)

close column_scan
deallocate column_scan
--
--Table completed
--
fetch table_scan into @.tab_name, @.id
set @.tab_err = @.@.FETCH_STATUS
end

close table_scan

deallocate table_scan