Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Jaybee from his castle
Yak Posting Veteran
64 Posts |
 Posted - 2006-05-29 : 17:19:01
|
| Hi all,I've got two tables (Client and Contacts), in two separate databases, on the same server, that need to be cleaned and merged with an Excel file (that also needs to be cleaned and merged) into one large csv file.Without having looked at the data, the first task seems to be to show the users some 'top 100 rows' sample data so I can nail down which of their criteria apply to which columns (I think the company who supplied the system may have gone bust, and we have no docu from them). Both tables have at least 15 columns, which is a pain to print out and tile up. Then, I'd also need to import the Excel data - which shouldn't be a problem.After that, I need to select 'firstname', 'lastname' and 'email_address' (although these column names might well vary between the three tables!) from each table, so that for example, anyone in the 'Client' table who bought a day ticket OR a week ticket etc (and there could well be a column for each) appears in the result set, but NOBODY is returned who doesn't have a valid email address.Now, I know that I've got to take a look at the table structures, but before I do, just a few questions?What would be the best way to define a correct email address within SQL? I presume that it must contain a dot and an '@', both preceeded and succeeded by a minimum of one character?What's the best way to print out the sample data for user review and readability?What should the SQL look like, roughly?Any 'gotcha's I need to watch out for? Thanks in advance, Jaybee. |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2006-06-07 : 12:10:13
|
| To merge he two tables, use UNION ALL.Select fname FirstName, lname LastName, street StreetAddress from sqlserverunion allselect fn, l_name, str_adr from xltable |
 |
|
|
RyanRandall
Master Smack Fu Yak Hacker
1074 Posts |
Posted - 2006-06-07 : 12:13:09
|
quote:
What would be the best way to define a correct email address within SQL? I presume that it must contain a dot and an '@', both preceeded and succeeded by a minimum of one character?
http://vyaskn.tripod.com/handling_email_addresses_in_sql_server.htmRyan Randallwww.monsoonmalabar.com London-based IT consultancy Solutions are easy. Understanding the problem, now, that's the hard part. |
|
|
|
|
|
|
|
|
How would you approach this data cleansing project