April 27, 2012 at 4:37 pm
I guess am not getting your question because i don't need to validate anything all i need is a query that returns any Employees (id, ssn, and name) with an invalid SSN along with the company name.
April 27, 2012 at 5:11 pm
mwaniki1 (4/27/2012)
I guess am not getting your question because i don't need to validate anything all i need is a query that returns any Employees (id, ssn, and name) with an invalid SSN along with the company name.
I am sorry if your own words are confusing to you as well as me:
Basically looking to use a JOIN on the two tables to find invalid ssn using the invalid rules listed.
April 27, 2012 at 6:22 pm
mwaniki1 (4/27/2012)
I guess am not getting your question because i don't need to validate anything all i need is a query that returns any Employees (id, ssn, and name) with an invalid SSN along with the company name.
you dont need to validate any thing yet you need invalid SSN's?? in order for something to be invalid it has to be validated against a rule set.
to lynn i think what he is saying is he needs a select like the following sudo select, and mwaniki let us know if im correct:
SELECT CompanyName, EmployeeName, SSN, 'Invalid SSN'
FROM employee e
inner join company c
on e.companyID = c.companyID
WHERE <ssn validation goes here>
For performance Issues see how we like them posted here: How to Post Performance Problems - Gail Shaw[/url]
Need to Split some strings? Jeff Moden's DelimitedSplit8K[/url]
Jeff Moden's Cross tab and Pivots Part 1[/url]
Jeff Moden's Cross tab and Pivots Part 2[/url]
April 27, 2012 at 7:11 pm
capn.hector (4/27/2012)
mwaniki1 (4/27/2012)
I guess am not getting your question because i don't need to validate anything all i need is a query that returns any Employees (id, ssn, and name) with an invalid SSN along with the company name.you dont need to validate any thing yet you need invalid SSN's?? in order for something to be invalid it has to be validated against a rule set.
to lynn i think what he is saying is he needs a select like the following sudo select, and mwaniki let us know if im correct:
SELECT CompanyName, EmployeeName, SSN, 'Invalid SSN'FROM employee e
inner join company c
on e.companyID = c.companyID
WHERE <ssn validation goes here>
Thanks cap'n. I was pretty sure what he wanted, just his wording was making me cautious. Have to remember that there are times when perceptions can be wrong.
April 28, 2012 at 9:24 am
Thanks Lynn that is what I was looking for as a start. As for the validation, that is why I included the rules fron US.gov website for how to determine invalid ssn. The JOIN is good but the birthday date column is vital for the query to work the problem is am having trouble where or how to incorporate it into the query. Any help with that would greatly help. Thanks!
April 28, 2012 at 9:34 am
tblCompany co (PK, varchar(10), not null)
name (varchar (50), not null)
tblEmployees co (PK, FK, varchar(10), not null)
id (PK, varchar(10), not null)
ssn (varchar(11), not null)
lastName (varchar(40), not null)
firstName (varchar(40), not null)
birthday (datetime, null)
According to the US.gov website, the following rules determine how to validate invalid SSN.
An invalid or impossible SSN is one that we never assigned.
We have never assigned an SSN with the first three digits of: 000 666 900 series Additionally, prior to June 25, 2011, we never assigned an SSN with the first three digits of: 0000
000 666 Above 772 in the 700 series 800 series 900 series We have never assigned an SSN with the second two digits of 00 or the last four digits of 0000.
April 28, 2012 at 10:38 am
mwaniki1 (4/28/2012)
tblCompany co (PK, varchar(10), not null)name (varchar (50), not null)
tblEmployees co (PK, FK, varchar(10), not null)
id (PK, varchar(10), not null)
ssn (varchar(11), not null)
lastName (varchar(40), not null)
firstName (varchar(40), not null)
birthday (datetime, null)
According to the US.gov website, the following rules determine how to validate invalid SSN.
An invalid or impossible SSN is one that we never assigned.
We have never assigned an SSN with the first three digits of: 000 666 900 series Additionally, prior to June 25, 2011, we never assigned an SSN with the first three digits of: 0000
000 666 Above 772 in the 700 series 800 series 900 series We have never assigned an SSN with the second two digits of 00 or the last four digits of 0000.
Gosh... GSquared's code looks like a pretty good start to me. Have you tried any of that?
--Jeff Moden
Change is inevitable... Change for the better is not.
April 30, 2012 at 7:25 am
Birthday isn't a valid test for SSNs. It's only very recently that they started issuing them that early. I got mine when I was 18, when I first registered for the draft and to vote, for example, and that was normal at that time. Immigrants might have a birthdate that's decades ago and an SSN assigned this morning, as another example. Assuming that birthdate = SSN assignment date is not a good assumption.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
August 15, 2015 at 10:47 am
This reply has been reported for inappropriate content.
Hello all dear customers , My ICQ Number 651354130
+ I am a Big Hacker and i have a lot infor fresh for sell everyday
+ I want sell cc + cVv good fresh , ssn dob year kids 1990 come 2010 , same last name and find state
+ I am looking for good customer business and honest for long time
SELL DUMPS TRACK 1 2 , CODE 101 - 201 , BIN BUSINESS , PLATINUM ...AND I HAVE SELL SOFTWARE WRITE TRACK INFOR GOOD WORK
- B4246315185161583^VAUGHAN/BOBBY ^1612101110181100000000944000000*4246315185161583=161210111018944*VISA*BUSINESS*CREDIT*CHASE BANK USA N.A.*UNITED STATES
- B4280480000022241^LLC/MARCHING ANTS^1512101161280000000000728000000*4280480000022241=15121011612800007280*VISA*BUSINESS*DEBIT*CALIFORNIA C.U.*UNITED STATES
DUMPS TRACK 1/2 UK :
B4462724337874002^DWIGGINS/CHARLES ^1705101100001100000000551000000*4462724337874002=170510110000551*VISA*LLOYDS TSB BANK PLC*DEBIT*United Kingdom
B4543135178641429^LABELLE/RICHARD ^1603201170161100000000848000000*4543135178641429=160320117016848*VISA*DEBIT*NATIONWIDE BUILDING SOCIETY*United Kingdom
- Affter you payment for me with Bitcon or WU wait some minutes i check and send Dumps
- Refund time 24 hr affter you get Dumps i have bin BUSINESS,PLATINUM,SIGNATURE...CODE 101 201 CREDIT DEBIT
- You buy 5 dumps will a best price . i am sure my dumps i sell good valid 99%
-----------------------------------------------------------------
=> I SELL SSN + DOB valid verified , DOB KIDS 1990 > 2000... 2005, Same last name and CHECK STATE
+ PROVE SSN DOB VALID :
*RANDOM STATE :
|Darrell|P|Young|1323 Arthur Avenue|Kankakee|IL|60901|US|815-802-1333|Stallcup|2/22/1978|Visa|4556057473014801|765|10/2015|347-84-1247
|Yvette|R|Miller|1764 Vernon Street|La Mesa|CA|91941|US|760-607-5090|Dunn|7/6/1981|Visa|4716152763076185|440|12/2016|617-16-8389
|Rae|D|Walker|4918 Wexford Way|Rock Hill|SC|29730|US|803-329-5541|Ortiz|8/31/1972|MasterCard|5448999641624103|409|5/2017|657-03-6758
+ Check DOB KIDS 1990 to 2010 :
|Michael|G|Jewell|2926 Heritage Road|Fresno|CA|93721|US|559-696-7153|Sheetz|2/25/1990|MasterCard|5453242214262684|038|1/2016|609-52-6833
1657|Elizabeth|A|Speck|4876 Blane Street|Saint Louis|MO|63108|US|314-521-3395|Wilson|6/21/1990|Visa|4929776832185735|014|5/2014|489-30-9849
+ SSN DOB SAME LAST NAME :
|timothy|d|smith|595663601|072885|97855242|3707|maple ln.|murfreesboro|tn|37129|6152757734|smitht85@msn.com
|David|G|Smith|253310215|122177|1000069195|3210|Forest Terrace|Anderson|In|46013|7656491708|dgs1221@aol.com
---------
|belinda|n|davis|438778046|121572|0007654928|720|hwy 577|winnsboro|La|71295|3187220069|webjohnsons@aol.com
|Adam|C|Davis|251553296|040878|007355444|8805|neely ferry rd|Laurens|sc|29360|8646845321|tlw0408@yahoo.com
==> I SURE SSN DOB SELL FOR YOU FRESH VALID and Never use before
RULES BUSINESS :
* I ACCEPT PAYMENT WESTERN UNION (WU) BITCON (BTC) WEDMONEY (WMZ)
* DONT CONTACT ME AND ASK TEST FREE FIRST , NOBODY WORKING FREE TIME
* AFFTER PAYMENT FOR ME WAIT FEW MINUTES YOU WILL GOT INFOR
* Seller dont add me , Replace time - 24 hour after purchase Dumps
* BUSINESS WITH ME NEED => TRUST - HONEST .
* WE are serious business, looking for a long-term relationship
I need real buys and serious business, I will business good with all customer for trust ...
Infor Contact me Business : (I only use one ICQ and Gmail - Online 24/7)
----ICQ Number : 651354130
----GMail : verified.carding@gmail.com
March 12, 2019 at 8:02 am
I've been using the following and it's proved successful.
WHERE LEN(SSN) <> 9
OR LEFT(SSN,1) IN ('8')
OR LEFT(SSN,2) IN ('74','78','79')
OR LEFT(SSN,3) IN ('666','734','735','736','737','738','739','773','774','775','776','777','778','779')
OR LEFT(SSN,3) = '000'
OR SUBSTRING(SSN,4,2) = '00'
OR SUBSTRING(SSN,6,4) = '0000'
--OR SSN IS NULL
OR SSN LIKE '%[a-z]%'
Thoughts?
March 12, 2019 at 8:33 am
glenn.rollins3 - Tuesday, March 12, 2019 8:02 AMI've been using the following and it's proved successful.
WHERE LEN(SSN) <> 9
OR LEFT(SSN,1) IN ('8')
OR LEFT(SSN,2) IN ('74','78','79')
OR LEFT(SSN,3) IN ('666','734','735','736','737','738','739','773','774','775','776','777','778','779')
OR LEFT(SSN,3) = '000'
OR SUBSTRING(SSN,4,2) = '00'
OR SUBSTRING(SSN,6,4) = '0000'
--OR SSN IS NULL
OR SSN LIKE '%[a-z]%'Thoughts?
I think that storing SSN in plain text is criminal behavior.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
March 12, 2019 at 11:11 am
Sean Lange - Tuesday, March 12, 2019 8:33 AMglenn.rollins3 - Tuesday, March 12, 2019 8:02 AMI've been using the following and it's proved successful.
WHERE LEN(SSN) <> 9
OR LEFT(SSN,1) IN ('8')
OR LEFT(SSN,2) IN ('74','78','79')
OR LEFT(SSN,3) IN ('666','734','735','736','737','738','739','773','774','775','776','777','778','779')
OR LEFT(SSN,3) = '000'
OR SUBSTRING(SSN,4,2) = '00'
OR SUBSTRING(SSN,6,4) = '0000'
--OR SSN IS NULL
OR SSN LIKE '%[a-z]%'Thoughts?
I think that storing SSN in plain text is criminal behavior.
Preach it, Brother!!! Storing any form of Tax ID in an unencrypted fashion is just flat out wrong and a violation of every PII rule I've ever come across.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 12, 2019 at 11:36 am
… and if the company gets security audited, that's going to be serious. and expensive.
March 12, 2019 at 11:36 am
Jeff Moden - Tuesday, March 12, 2019 11:11 AMSean Lange - Tuesday, March 12, 2019 8:33 AMglenn.rollins3 - Tuesday, March 12, 2019 8:02 AMI've been using the following and it's proved successful.
WHERE LEN(SSN) <> 9
OR LEFT(SSN,1) IN ('8')
OR LEFT(SSN,2) IN ('74','78','79')
OR LEFT(SSN,3) IN ('666','734','735','736','737','738','739','773','774','775','776','777','778','779')
OR LEFT(SSN,3) = '000'
OR SUBSTRING(SSN,4,2) = '00'
OR SUBSTRING(SSN,6,4) = '0000'
--OR SSN IS NULL
OR SSN LIKE '%[a-z]%'Thoughts?
I think that storing SSN in plain text is criminal behavior.
Preach it, Brother!!! Storing any form of Tax ID in an unencrypted fashion is just flat out wrong and a violation of every PII rule I've ever come across.
Thanks, I'll pass it on to the company's IT department.
March 12, 2019 at 12:00 pm
glenn.rollins3 - Tuesday, March 12, 2019 11:36 AMJeff Moden - Tuesday, March 12, 2019 11:11 AMSean Lange - Tuesday, March 12, 2019 8:33 AMglenn.rollins3 - Tuesday, March 12, 2019 8:02 AMI've been using the following and it's proved successful.
WHERE LEN(SSN) <> 9
OR LEFT(SSN,1) IN ('8')
OR LEFT(SSN,2) IN ('74','78','79')
OR LEFT(SSN,3) IN ('666','734','735','736','737','738','739','773','774','775','776','777','778','779')
OR LEFT(SSN,3) = '000'
OR SUBSTRING(SSN,4,2) = '00'
OR SUBSTRING(SSN,6,4) = '0000'
--OR SSN IS NULL
OR SSN LIKE '%[a-z]%'Thoughts?
I think that storing SSN in plain text is criminal behavior.
Preach it, Brother!!! Storing any form of Tax ID in an unencrypted fashion is just flat out wrong and a violation of every PII rule I've ever come across.
Thanks, I'll pass it on to the company's IT department.
No... That's almost useless. Pass it on to the CEO, the head of Marketing (while explaining the black eye to the company and resulting future lost business if there's a breach), the head of Finance (how much the breach will actually cost, which is more than you can currently imagine), AND the Director of IT (explain how they might not ever be able to find another job in IT if a breach occurs). If the company is public, pass all that bad news on to the Board of Directors, as well. The rank stupidity of things like clear text SSNs has got to stop.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 16 through 30 (of 44 total)
You must be logged in to reply to this topic. Login to reply