• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 430
  • Last Modified:

Detecting junk data

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
friskyweasel
Asked:
friskyweasel
  • 3
  • 3
  • 2
3 Solutions
 
Sham HaqueSenior SAP CRM ConsultantCommented:
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
 
Sham HaqueSenior SAP CRM ConsultantCommented:
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
 
friskyweaselAuthor Commented:
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
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
ThinkPaperIT ConsultantCommented:
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
 
ThinkPaperIT ConsultantCommented:
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
 
friskyweaselAuthor Commented:
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
 
friskyweaselAuthor Commented:
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
 
Sham HaqueSenior SAP CRM ConsultantCommented:
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: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now