Solved

exporting ssn to sql server

Posted on 2013-01-08
15
330 Views
Last Modified: 2013-01-09
The format in excel has a special SSN format, however when I import it ot SQL , it removes all the "-"  eg.xxx-xx-xxx and also if SSN  begins with 0, it gets truncated and lose that digit.
What is the technique to import SSN without losing the "-" in the process?
0
Comment
Question by:zachvaldez
[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
  • 7
  • 3
  • 2
  • +2
15 Comments
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 38756216
Don't know if you can retain the "-"s or not.

But you can prevent the leading 0s from being removed by defining the column as varchar() rather than numeric for the transfer.
0
 

Author Comment

by:zachvaldez
ID: 38756239
if so, what is the update formula  for the column if it gets imported as
'xxxxxxxxx' so it would convert to 'xxx-xx-xxxx'

and if it loses 0 in the beginning adds '0'.. thanks
0
 
LVL 32

Expert Comment

by:awking00
ID: 38756348
select substr(lpad(ssn,9,'0'),1,3)||'-'||substr(lpad(ssn,9,'0'),4,2)||'-'||substr(lpad(ssn,9,'0'),6)
0
Free eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

 
LVL 69

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 100 total points
ID: 38756356
Immediately after any import, you could run UPDATEs, like this:


UPDATE dbo.tablename
SET ssn_column = RIGHT(REPLICATE('0', 9) + ssn_column, 9)
WHERE
    LEN(ssn_column) < 9

UPDATE dbo.tablename
SET ssn_column = LEFT(ssn_column, 3) + '-' + SUBSTRING(ssn_column, 4, 2) + '-' + SUBSTRING(ssn_column, 6, 100)
WHERE
    ssn_column NOT LIKE '___[-]__[-]____%'
0
 
LVL 32

Expert Comment

by:awking00
ID: 38756391
Sorry, was thinking Oracle syntax instead of sql server where right(replicate(... replaces lpad and substring replaces substr and + replaces ||. ScottPletcher has your answer.
0
 

Author Comment

by:zachvaldez
ID: 38756583
Im getting error saying Replicate function requires 2 argument(s)
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 38756602
Hmm, something's not coded right then, because the REPLICATE part above:

select
REPLICATE('0', 9)

select
RIGHT(REPLICATE('0', 9) + ssn_column, 9)
from (
    select '123456' as ssn_column
) as test_data
0
 

Author Comment

by:zachvaldez
ID: 38756619
Ok I found my mistake. thanks. It only corrected a few. I think its possible that an SSN could start with '00',000'..
0
 
LVL 92

Accepted Solution

by:
Patrick Matthews earned 150 total points
ID: 38756732
When you apply a number format to a true number in Excel, it is the actual value and not the displayed value that gets stored.

The easiest way to approach this, in my opinion, would have been to add a column in your Excel worksheet, and use a formula to explicitly convert the SSN to a text value, such as:

=TEXT(A2,"000-00-0000")

That will preserve the dashes, as well as any leading zeroes as needed.
0
 

Author Comment

by:zachvaldez
ID: 38756865
What data type should I set the new column in sql server. when I imported it , they were all Null even though they have the correct format 'xxx-xx-xxxx' afte using
=text(a2,"000-00-0000")
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 38756927
You would have to use a data type that supports text.

At the very least you would want [n][var]char(11), depending on whether you want ANSI or Unicode, and whether you want fixed or dynamic length.

If there is any possibility that you will be storing other items in that column (such as a business tax ID number), you may want to specify a longer length.
0
 

Author Comment

by:zachvaldez
ID: 38756996
Thanks,Igot it!!
0
 
LVL 21

Assisted Solution

by:Alpesh Patel
Alpesh Patel earned 70 total points
ID: 38758501
Just convert the Field to Text in Excel and Import it using Wizard.
0
 

Author Comment

by:zachvaldez
ID: 38758607
I didn't think. Yeah,that probably the simplest solution
0
 

Author Closing Comment

by:zachvaldez
ID: 38759822
iT WORKED!
0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Suggested Solutions

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…
This video shows how to use Hyena, from SystemTools Software, to update 100 user accounts from an external text file. View in 1080p for best video quality.

752 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