[lnkForumImage]
TotalShareware - Download Free Software
Confronta i prezzi di migliaia di prodotti.
Asp Forum
 Home | Login | Register | Search 


 

Forums >

microsoft.public.sqlserver.programming

Valid Social Security Number in T-SQL

dba

3/30/2007 5:05:00 PM

This works as a where clause in a query, but I'm trying to change it
into a UDF to find valid social security numbers through SQL. I'm
getting a syntax error and I'm not sure why?

create function dbo.ufn_valid_ssn(@ssn varchar(11))
returns varchar(11)
as
begin
(@ssn like '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]')
or
/* 3 integers hypen 2 integers hypen 4 integers */
(@ssn like '[0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9][0-9][0-9]')
or
/* 3 integers space 2 integers space 4 integers */
(@ssn like '[0-9][0-9][0-9] [0-9][0-9] [0-9][0-9][0-9][0-9]')
or
/* 3 integers forwardslash 2 integers forwardslash 4 integers */
(@ssn like '[0-9][0-9][0-9]/[0-9][0-9]/[0-9][0-9][0-9][0-9]')
or
/* 3 integers backslash 2 integers backslash 4 integers */
(@ssn like '[0-9][0-9][0-9]\[0-9][0-9]\[0-9][0-9][0-9][0-9]')
end
3 Answers

Aaron [SQL Server MVP]

3/30/2007 5:24:00 PM

0

CREATE FUNCTION dbo.ufn_valid_ssn
(
@ssn VARCHAR(11)
)
RETURNS BIT
AS
RETURN (SELECT CASE
WHEN @ssn LIKE ... THEN 1
WHEN @ssn LIKE ... THEN 1
WHEN @ssn LIKE ... THEN 1
WHEN @ssn LIKE ... THEN 1
ELSE 0 END);
END
GO

Now in your WHERE clause you would say

WHERE dbo.ufn_valid_ssn(SSN_column) = 1;

--
Aaron Bertrand
SQL Server MVP
http://www.sq...
http://www.aspfa...

"dba" <bryanmurtha@gmail.com> wrote in message
news:1175274314.726655.29610@n59g2000hsh.googlegroups.com...
> This works as a where clause in a query, but I'm trying to change it
> into a UDF to find valid social security numbers through SQL. I'm
> getting a syntax error and I'm not sure why?
>
> create function dbo.ufn_valid_ssn(@ssn varchar(11))
> returns varchar(11)
> as
> begin
> (@ssn like '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]')
> or
> /* 3 integers hypen 2 integers hypen 4 integers */
> (@ssn like '[0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9][0-9][0-9]')
> or
> /* 3 integers space 2 integers space 4 integers */
> (@ssn like '[0-9][0-9][0-9] [0-9][0-9] [0-9][0-9][0-9][0-9]')
> or
> /* 3 integers forwardslash 2 integers forwardslash 4 integers */
> (@ssn like '[0-9][0-9][0-9]/[0-9][0-9]/[0-9][0-9][0-9][0-9]')
> or
> /* 3 integers backslash 2 integers backslash 4 integers */
> (@ssn like '[0-9][0-9][0-9]\[0-9][0-9]\[0-9][0-9][0-9][0-9]')
> end
>


--CELKO--

3/30/2007 5:55:00 PM

0

>> This works as a where clause in a query, but I'm trying to change it into a UDF to find valid social security numbers through SQL. m<<

That is a mistake. Make it into a CHECK() constraint and assure that
you have clean data, formatted in one and only one way in the
schema. And your code will be portable, too!

You are depending on *every* application -- present and future -- to
do the work for you and that nobody will come into the DB via QA or
other tools. Too much risk.

Next what you posted does not really work. The SSN is divided as
follows: the area number (first three digits), group number (fourth
and fifth digits), and serial number (last four digits).

To determine if an SSN is invalid consider the following: No SSNs with
an area number in the 800 or 900 series, or 000 area number, have been
assigned. No SSNs with an area number above 772 have been assigned in
the 700 series.

No SSN's with a "00" group number or "0000" serial number have been
assigned. No SSNs with an area number of "666" have been or will be
assigned (Really -- Mark of the beast and all that crap!). Information
about the SSN and SSNs that have been assigned is available at "High
Group List and Other Ways to Determine if an SSN is Valid" on their
website.

It is hard to code and it needs to be updated at least once a year as
new numbers are issued and people die to be 100% correct. Google
around for open source validators.

Aaron's code has an additional problem. Are you going to use the
proprietary BIT as a Boolean flag in applications? Does your app
language use +1 or -1 for TRUE? Or is 0 used for TRUE? The answer is
"yes" for all three situations.

I clean up this kind of stuff for a living. It is very hard work, not
a few lines of UDF code or even a simple CHECK() constraint.

Anith Sen

3/30/2007 10:33:00 PM

0

Just to add to the above comments, see:
http://www.searchbug.com/peoplefinder/invalid-social-security-nu...


--
Anith