Convert SSN (varchar) to Number

Hello, I'm trying to get the Max number from a range of Social Security
numbers in my SQL 2000 Customer table. The SSNs as stored as
varchar(9).
I'm supposed to get the SSN with the 'highest' numbers from 001330000
to 001379999 range.
I know that number should be 001340062, but I never got it. I've tried
to convert it and cast it, but so far I haven't been able to do so.
When I converted it, I got only 1340062 (it was missing the first 2
zeroes).
We're creating fakes SSNs for people who don't have one and I'm
supposed to know which one is the max we've created so far and then add
1 to it and so on and so on. This worked in Access, but when we
upgraded to SQL it didn't work anymore.
Any help would be appreciated it.
Thanks.Is casting really necessary? Could you try:
select
max (SSN)
from
MyTable
where
SSN between '001330000' and '001379999'
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
<ILCSP@.NETZERO.NET> wrote in message
news:1151436829.800467.247610@.p79g2000cwp.googlegroups.com...
Hello, I'm trying to get the Max number from a range of Social Security
numbers in my SQL 2000 Customer table. The SSNs as stored as
varchar(9).
I'm supposed to get the SSN with the 'highest' numbers from 001330000
to 001379999 range.
I know that number should be 001340062, but I never got it. I've tried
to convert it and cast it, but so far I haven't been able to do so.
When I converted it, I got only 1340062 (it was missing the first 2
zeroes).
We're creating fakes SSNs for people who don't have one and I'm
supposed to know which one is the max we've created so far and then add
1 to it and so on and so on. This worked in Access, but when we
upgraded to SQL it didn't work anymore.
Any help would be appreciated it.
Thanks.|||Take a look at this
select RIGHT('000000000' + CONVERT(VARCHAR(9),12345),9)
change 12345 to your output
Denis the SQL Menace
http://sqlservercode.blogspot.com/
ILCSP@.NETZERO.NET wrote:
> Hello, I'm trying to get the Max number from a range of Social Security
> numbers in my SQL 2000 Customer table. The SSNs as stored as
> varchar(9).
> I'm supposed to get the SSN with the 'highest' numbers from 001330000
> to 001379999 range.
> I know that number should be 001340062, but I never got it. I've tried
> to convert it and cast it, but so far I haven't been able to do so.
> When I converted it, I got only 1340062 (it was missing the first 2
> zeroes).
> We're creating fakes SSNs for people who don't have one and I'm
> supposed to know which one is the max we've created so far and then add
> 1 to it and so on and so on. This worked in Access, but when we
> upgraded to SQL it didn't work anymore.
> Any help would be appreciated it.
> Thanks.|||What does Homeland Security think about you creating 'fake SSNs'?
g,d,r
--
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another Certification Exam
<ILCSP@.NETZERO.NET> wrote in message
news:1151436829.800467.247610@.p79g2000cwp.googlegroups.com...
> Hello, I'm trying to get the Max number from a range of Social Security
> numbers in my SQL 2000 Customer table. The SSNs as stored as
> varchar(9).
> I'm supposed to get the SSN with the 'highest' numbers from 001330000
> to 001379999 range.
> I know that number should be 001340062, but I never got it. I've tried
> to convert it and cast it, but so far I haven't been able to do so.
> When I converted it, I got only 1340062 (it was missing the first 2
> zeroes).
> We're creating fakes SSNs for people who don't have one and I'm
> supposed to know which one is the max we've created so far and then add
> 1 to it and so on and so on. This worked in Access, but when we
> upgraded to SQL it didn't work anymore.
> Any help would be appreciated it.
> Thanks.
>|||Arnie Rowland wrote:
> What does Homeland Security think about you creating 'fake SSNs'?
> g,d,r
>
Uh-oh, now you're forever associated with the phrase "fake SSN" - NSA
will be looking for you... :-)
Oh crap, now me too...|||What if you create a 'fake' SSN for someone and then later on someone
signs up with that SSN, then what happens? Hint: Big Headache since
you have to assign a new 'fake' SSN to the old person and changes this
probably all over the place
Better to use XXXX00001 or some other Format like FAKE00001 etc etc
Denis the SQL Menace
http://sqlservercode.blogspot.com/
Arnie Rowland wrote:
> What does Homeland Security think about you creating 'fake SSNs'?
> g,d,r
> --
> Arnie Rowland, YACE*
> "To be successful, your heart must accompany your knowledge."
> *Yet Another Certification Exam
>
> <ILCSP@.NETZERO.NET> wrote in message
> news:1151436829.800467.247610@.p79g2000cwp.googlegroups.com...
> > Hello, I'm trying to get the Max number from a range of Social Security
> > numbers in my SQL 2000 Customer table. The SSNs as stored as
> > varchar(9).
> >
> > I'm supposed to get the SSN with the 'highest' numbers from 001330000
> > to 001379999 range.
> >
> > I know that number should be 001340062, but I never got it. I've tried
> > to convert it and cast it, but so far I haven't been able to do so.
> > When I converted it, I got only 1340062 (it was missing the first 2
> > zeroes).
> >
> > We're creating fakes SSNs for people who don't have one and I'm
> > supposed to know which one is the max we've created so far and then add
> > 1 to it and so on and so on. This worked in Access, but when we
> > upgraded to SQL it didn't work anymore.
> >
> > Any help would be appreciated it.
> >
> > Thanks.
> >|||Hello all, I used Tom's way and it works great. Thanks for replying.
I think I should have not used the phrase fake SSNs, but I guess I just
wanted to get some attention.
:-)
Tom Moreau wrote:
> Is casting really necessary? Could you try:
> select
> max (SSN)
> from
> MyTable
> where
> SSN between '001330000' and '001379999'
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Toronto, ON Canada
> .
> <ILCSP@.NETZERO.NET> wrote in message
> news:1151436829.800467.247610@.p79g2000cwp.googlegroups.com...
> Hello, I'm trying to get the Max number from a range of Social Security
> numbers in my SQL 2000 Customer table. The SSNs as stored as
> varchar(9).
> I'm supposed to get the SSN with the 'highest' numbers from 001330000
> to 001379999 range.
> I know that number should be 001340062, but I never got it. I've tried
> to convert it and cast it, but so far I haven't been able to do so.
> When I converted it, I got only 1340062 (it was missing the first 2
> zeroes).
> We're creating fakes SSNs for people who don't have one and I'm
> supposed to know which one is the max we've created so far and then add
> 1 to it and so on and so on. This worked in Access, but when we
> upgraded to SQL it didn't work anymore.
> Any help would be appreciated it.
> Thanks.|||"Arnie Rowland" <arnie@.1568.com> wrote in message
news:eEVMRNimGHA.4700@.TK2MSFTNGP05.phx.gbl...
> What does Homeland Security think about you creating 'fake SSNs'?
> g,d,r
> --
> Arnie Rowland, YACE*
I would think they are already contacting
CoreComm - Voyager, Inc
East Lansing, Michigan|||You're guarenteed to be high on the DHS/NSA watch list!
--
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another Certification Exam
<ILCSP@.NETZERO.NET> wrote in message
news:1151438702.180395.194640@.u72g2000cwu.googlegroups.com...
> Hello all, I used Tom's way and it works great. Thanks for replying.
> I think I should have not used the phrase fake SSNs, but I guess I just
> wanted to get some attention.
> :-)
>
> Tom Moreau wrote:
>> Is casting really necessary? Could you try:
>> select
>> max (SSN)
>> from
>> MyTable
>> where
>> SSN between '001330000' and '001379999'
>> --
>> Tom
>> ----
>> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
>> SQL Server MVP
>> Toronto, ON Canada
>> .
>> <ILCSP@.NETZERO.NET> wrote in message
>> news:1151436829.800467.247610@.p79g2000cwp.googlegroups.com...
>> Hello, I'm trying to get the Max number from a range of Social Security
>> numbers in my SQL 2000 Customer table. The SSNs as stored as
>> varchar(9).
>> I'm supposed to get the SSN with the 'highest' numbers from 001330000
>> to 001379999 range.
>> I know that number should be 001340062, but I never got it. I've tried
>> to convert it and cast it, but so far I haven't been able to do so.
>> When I converted it, I got only 1340062 (it was missing the first 2
>> zeroes).
>> We're creating fakes SSNs for people who don't have one and I'm
>> supposed to know which one is the max we've created so far and then add
>> 1 to it and so on and so on. This worked in Access, but when we
>> upgraded to SQL it didn't work anymore.
>> Any help would be appreciated it.
>> Thanks.
>|||Just FYI, 001-xx-xxxx codes are reserved by New Hampshire. I wouldn't
advise assigning "fake SSN's" to begin with, but I *definitely* wouldn't
assign them in a range that is currently in use by a state. That's just
asking for trouble.
According to the SSA, any valid number beginning with "000" will never be a
valid SSN. Here are some links to help with the business rules:
http://www.socialsecurity.gov/employer/highgroup.txt
http://www.socialsecurity.gov/employer/stateweb.htm
Given the three numbers you've passed in, the SELECT below will grab the
MAX(). It sounds like you want to add one and left pad it with zeroes.
Here's a sample:
CREATE FUNCTION dbo.AddOneAndPadSSN(@.ssn VARCHAR(9))
RETURNS VARCHAR(9)
AS
BEGIN
DECLARE @.iSSN INT
SELECT @.iSSN = CAST(@.ssn AS INT) + 1
DECLARE @.newSSN VARCHAR(9)
SELECT @.newSSN = RIGHT ('000000000' + CAST(@.iSSN AS VARCHAR(9)), 9)
RETURN @.newSSN
END
GO
CREATE TABLE #temp(ssn VARCHAR(9))
INSERT INTO #temp(ssn)
SELECT '001330000'
UNION SELECT '001379999'
UNION SELECT '001340062'
SELECT dbo.AddOneAndPadSSN(MAX(ssn))
FROM #temp
DROP TABLE #temp
<ILCSP@.NETZERO.NET> wrote in message
news:1151436829.800467.247610@.p79g2000cwp.googlegroups.com...
> Hello, I'm trying to get the Max number from a range of Social Security
> numbers in my SQL 2000 Customer table. The SSNs as stored as
> varchar(9).
> I'm supposed to get the SSN with the 'highest' numbers from 001330000
> to 001379999 range.
> I know that number should be 001340062, but I never got it. I've tried
> to convert it and cast it, but so far I haven't been able to do so.
> When I converted it, I got only 1340062 (it was missing the first 2
> zeroes).
> We're creating fakes SSNs for people who don't have one and I'm
> supposed to know which one is the max we've created so far and then add
> 1 to it and so on and so on. This worked in Access, but when we
> upgraded to SQL it didn't work anymore.
> Any help would be appreciated it.
> Thanks.
>|||Nobody here but us wannabe fashion clothes reps... :)
"Michael Kujawa" <nof at kujawas dot net> wrote in message
news:OH8N2TimGHA.3632@.TK2MSFTNGP03.phx.gbl...
> "Arnie Rowland" <arnie@.1568.com> wrote in message
> news:eEVMRNimGHA.4700@.TK2MSFTNGP05.phx.gbl...
>
>> What does Homeland Security think about you creating 'fake SSNs'?
>> g,d,r
>> --
>> Arnie Rowland, YACE*
>
> I would think they are already contacting
> CoreComm - Voyager, Inc
> East Lansing, Michigan
>|||One would think they don't have enough people to cover the newsgroups, what
with trying to keep up with all of our phone calls :)
"Tracy McKibben" <tracy@.realsqlguy.com> wrote in message
news:uTsVwOimGHA.856@.TK2MSFTNGP03.phx.gbl...
> Arnie Rowland wrote:
>> What does Homeland Security think about you creating 'fake SSNs'?
>> g,d,r
> Uh-oh, now you're forever associated with the phrase "fake SSN" - NSA will
> be looking for you... :-)
> Oh crap, now me too...|||SSN are not as aggressively validated in the States the way they are in
Canada. With SSI numbers (the Canadian equivalent of SSNs) your number is
validated along with your birthdate before your first pay check is cut.
Providing an employer with a fake SSI number will only generate a request
for validation from Revenue Canada if the name and birthdate so not match
and the SSI number is not already in the system.
In America you have to prove your authorization to work in the States which
may or may not include you displaying a Social Security card to your
employer. If you provide them with a bogus number and they do not request to
see the card at some point in time the Social Security Administration may
make a request of your employer for validation of the number you supplied at
some point down the road - normally a year down the road.
Social Security Numbers have no checksum on them the way credit card numbers
do. The first three digits normally indicate where it was issued, and
immigrants typically receive their numbers from a different bank. Locales
may exhaust their number allotments before other locales and they may pull
from the same number pool as a less populous area.
Please refer to this document for more information.
http://en.wikipedia.org/wiki/Social_security_number#Valid_SSNs
I had a friend who had a completely bogus SSN for years with no problems.
Most frequently you run into problems when you and someone else uses the
same number, but with different names for it.
--
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Mike C#" <xyz@.xyz.com> wrote in message
news:%23GbmMmimGHA.5052@.TK2MSFTNGP04.phx.gbl...
> Just FYI, 001-xx-xxxx codes are reserved by New Hampshire. I wouldn't
> advise assigning "fake SSN's" to begin with, but I *definitely* wouldn't
> assign them in a range that is currently in use by a state. That's just
> asking for trouble.
> According to the SSA, any valid number beginning with "000" will never be
> a valid SSN. Here are some links to help with the business rules:
> http://www.socialsecurity.gov/employer/highgroup.txt
> http://www.socialsecurity.gov/employer/stateweb.htm
> Given the three numbers you've passed in, the SELECT below will grab the
> MAX(). It sounds like you want to add one and left pad it with zeroes.
> Here's a sample:
> CREATE FUNCTION dbo.AddOneAndPadSSN(@.ssn VARCHAR(9))
> RETURNS VARCHAR(9)
> AS
> BEGIN
> DECLARE @.iSSN INT
> SELECT @.iSSN = CAST(@.ssn AS INT) + 1
> DECLARE @.newSSN VARCHAR(9)
> SELECT @.newSSN = RIGHT ('000000000' + CAST(@.iSSN AS VARCHAR(9)), 9)
> RETURN @.newSSN
> END
> GO
> CREATE TABLE #temp(ssn VARCHAR(9))
> INSERT INTO #temp(ssn)
> SELECT '001330000'
> UNION SELECT '001379999'
> UNION SELECT '001340062'
> SELECT dbo.AddOneAndPadSSN(MAX(ssn))
> FROM #temp
> DROP TABLE #temp
>
> <ILCSP@.NETZERO.NET> wrote in message
> news:1151436829.800467.247610@.p79g2000cwp.googlegroups.com...
>> Hello, I'm trying to get the Max number from a range of Social Security
>> numbers in my SQL 2000 Customer table. The SSNs as stored as
>> varchar(9).
>> I'm supposed to get the SSN with the 'highest' numbers from 001330000
>> to 001379999 range.
>> I know that number should be 001340062, but I never got it. I've tried
>> to convert it and cast it, but so far I haven't been able to do so.
>> When I converted it, I got only 1340062 (it was missing the first 2
>> zeroes).
>> We're creating fakes SSNs for people who don't have one and I'm
>> supposed to know which one is the max we've created so far and then add
>> 1 to it and so on and so on. This worked in Access, but when we
>> upgraded to SQL it didn't work anymore.
>> Any help would be appreciated it.
>> Thanks.
>|||Hey!! Bugger off! I wanted that job.
--
Arnie Rowland, YACE*
"To be successful, your heart must accompany your knowledge."
*Yet Another Certification Exam
"Mike C#" <xyz@.xyz.com> wrote in message
news:O%238n7mimGHA.1404@.TK2MSFTNGP05.phx.gbl...
> Nobody here but us wannabe fashion clothes reps... :)
> "Michael Kujawa" <nof at kujawas dot net> wrote in message
> news:OH8N2TimGHA.3632@.TK2MSFTNGP03.phx.gbl...
>> "Arnie Rowland" <arnie@.1568.com> wrote in message
>> news:eEVMRNimGHA.4700@.TK2MSFTNGP05.phx.gbl...
>>
>> What does Homeland Security think about you creating 'fake SSNs'?
>> g,d,r
>> --
>> Arnie Rowland, YACE*
>>
>> I would think they are already contacting
>> CoreComm - Voyager, Inc
>> East Lansing, Michigan
>>
>|||SSN can be aggressively validated. Where I used to work we actually ran
validations, and discovered some fascinating things. Like several people in
a 6-block radius of Los Angeles with the same SSN :) Employers here are
supposed to look at, and make copies of, your SSN card when they hire you
(I-9 form). Of course no one from the government ever checks up on their
I-9 forms, so employers don't really go out of their way to validate the
information given to them...
Anyway, some info about SSNs:
-The first three digits are normally assigned based on the state or
territory/protectorate belonging to the ZIP code you mailed your application
in from. Some are assigned to U.S. citizens abroad (U.S. Embassies,
Enumeration at Entry) and some were assigned by the Railroad Board back in
the day.
-Numbers beginning with 000, 666 or 900 - 999 will not be assigned by the
SSA (they're used for internal government agencies). Numbers with 00 in the
middle or 0000 at the end will not be issued.
-There are some groups of Americans who refuse to get SSN's. The Amish are
an example of these groups.
-The first SSN ever assigned was to John D. Sweeney, Jr., of New Rochelle,
NY.
-The lowest SSN ever assigned was 001-01-0001 to former N.H. Governor and
Social Security Board Chairman John G. Winant. He turned it down. Then it
was offered to John Campbell of the Federal Bureau of Old Age Benefits. He
also turned it down. Finally it was assigned to the first applicant from
New Hampshire: Grace D. Owen of Concord.
Don't ask why I know all this crap about SSN's :) My old job required a lot
of investigation into it for validation purposes. In fact, a while back I
posted a basic SSN validation web service to Code Project that performs all
of the basic validations, including high group check, group validation and a
check against the invalidated SSN list (those used in advertising and
invalidate by the SSA): http://www.codeproject.com/aspnet/ssnvalidator.asp.