Solved

Detecting junk data

Posted on 2007-11-15
8
420 Views
Last Modified: 2012-05-05
hello all -
our database platform is MS SQL Server 2005, and we have a database containing between 8-10 million records, primarily user data (first name, last name, email, phone, address, etc...)

I need to come up with a way for us to return records that are obviously incorrect data. I'd like to start with the name fields (first/last name), and go on to possibly include address, phone, email, etc...

Does anyone know of any prebuilt SQL functions that test for this invalid name, email, etc...? Or any documentation on the topic? I googled on sql server junk records, sql server data validation, etc... but haven't had a lot of luck

0
Comment
Question by:friskyweasel
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 2
8 Comments
 
LVL 18

Accepted Solution

by:
Sham Haque earned 450 total points
ID: 20290662
email is a fairly straightforward and well documented one.
I use this:


			CASE
			WHEN CHARINDEX(' ',LTRIM(RTRIM([Email]))) > 0 THEN 'UNKNOWN@UNKNOWN.COM'-- No embedded spaces
			WHEN LEFT(LTRIM([Email]),1) = '@' THEN 'UNKNOWN@UNKNOWN.COM'  -- '@' can't be the first character of an email address
			WHEN RIGHT(RTRIM([Email]),1) = '.' THEN 'UNKNOWN@UNKNOWN.COM' -- '.' can't be the last character of an email address
			WHEN CHARINDEX('.',[Email],CHARINDEX('@',[Email])) - CHARINDEX('@',[Email]) <=1 THEN 'UNKNOWN@UNKNOWN.COM' -- There must be a '.' after '@'
			WHEN LEN(LTRIM(RTRIM([Email]))) - LEN(REPLACE(LTRIM(RTRIM([Email])),'@','')) > 1 THEN 'UNKNOWN@UNKNOWN.COM'-- Only one '@' sign is allowed
			WHEN CHARINDEX('.',REVERSE(LTRIM(RTRIM([Email])))) < 3 THEN 'UNKNOWN@UNKNOWN.COM'-- Domain name should end with at least 2 character extension
			WHEN (CHARINDEX('.@',[Email]) > 0 AND CHARINDEX('..',[Email]) > 0) THEN 'UNKNOWN@UNKNOWN.COM'-- can't have patterns like '.@' and '..'
			WHEN CHARINDEX('@',[Email]) = 0 THEN 'UNKNOWN@UNKNOWN.COM'
			WHEN [Email] IS NULL THEN 'UNKNOWN@UNKNOWN.COM'
			ELSE [Email]
			END

Open in new window

0
 
LVL 18

Assisted Solution

by:Sham Haque
Sham Haque earned 450 total points
ID: 20290691
there are several address cleaning services out there - doing matching against electoral registers, credit files etc to produce valid mailable addresses - that would be your best bet.
For UK addresses, I could point you to some companies I've used before...

You can do a check for numbers and invalid punctuation characters in name columns for example:

WHERE PATINDEX('%[0-9][*/,$£%&()"!]%',NameField) > 0
0
 

Author Comment

by:friskyweasel
ID: 20290992
thanks guys for the input - both suggestions sound real good

any ideas on ways to sniff out names that are obviously incorrect, but don't contain oddball characters? in other words, your example should allow me to easily detect:
"!e544$%%^"  as a bad name....

but what if it's: "asdfdfdf"

i'm not sure if there's any known method for detecting this sort of thing or not....



0
Get Database Help Now w/ Support & Database Audit

Keeping your database environment tuned, optimized and high-performance is key to achieving business goals. If your database goes down, so does your business. Percona experts have a long history of helping enterprises ensure their databases are running smoothly.

 
LVL 16

Expert Comment

by:ThinkPaper
ID: 20291083
names would be hard to validate.. unless all you're looking for is character checking (i.e. doesn't contain numbers or symbols - besides the apostrophe or dash)..
for this type of thing, look up regular expressions.
0
 
LVL 16

Assisted Solution

by:ThinkPaper
ThinkPaper earned 50 total points
ID: 20291112
i haven't used this before.. but i would think the us post office has an accurate account of addresses..

you may be able to use their db to validate your addresses

http://www.usps.com/ncsc/addressinfo/addressinfomenu.htm?from=zclsearch&page=ais
0
 

Author Comment

by:friskyweasel
ID: 20291434
thanks thinkpaper for the suggestions - those will come in handy

gbshahaq - i wanted to ask if you could go into a little more detail/description of your solution for bad name data:

PATINDEX('%[0-9][*/,$£%&()"!]%',NameField) > 0

so i notice you have 2 sections in brackets [], one for numbers and one for stray characters...i assumed (wrongly i think) that this would evaluate to true if ANY of the conditions were met (contained numbers OR stray characters)....but when i run it as-is above, my results are much more limited than they are if i do this:

PATINDEX('%[0-9]%',NameField) > 0
OR
PATINDEX('%[*/,$£%&()"!]%',NameField) > 0

so i was wondering, is there a way to make this evaluate as an "OR" inside the PATINDEX function without actually having to split it out via sql as i have above? would it take a performance hit by doing it as i am above as opposed to doing everything in the PATINDEX function?
0
 

Author Comment

by:friskyweasel
ID: 20291568
this seems to have accomplished what i was asking above gbshahaq...
PATINDEX('%[0123456789*/,$£%&()"!]%',NameField) > 0

thanks for the help everyone - gbshahag i'm giving you 90% since i used your answers - throwing 10% to thinkpaper for the US postal service suggestion

thanks!
0
 
LVL 18

Expert Comment

by:Sham Haque
ID: 20291732
thanks for the points friskyweasel - glad you got the PATINDEX syntax correct...
do investigate Regular Expressions though - some clever bods have come up with a workable Reg Ex solution in T-SQL - and if you're using 2005, you can use CLR stored procedures - i found one in C# the other day that you could use.
They're useful for matching against patterns of data, such as :
is "asdfdfdf" a pronouncable word?

it gets very complicated though (too much for me)...
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

717 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question