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

exporting ssn to sql server

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
zachvaldez
Asked:
zachvaldez
  • 7
  • 3
  • 2
  • +2
3 Solutions
 
Scott PletcherSenior DBACommented:
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
 
zachvaldezAuthor Commented:
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
 
awking00Commented:
select substr(lpad(ssn,9,'0'),1,3)||'-'||substr(lpad(ssn,9,'0'),4,2)||'-'||substr(lpad(ssn,9,'0'),6)
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
Scott PletcherSenior DBACommented:
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
 
awking00Commented:
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
 
zachvaldezAuthor Commented:
Im getting error saying Replicate function requires 2 argument(s)
0
 
Scott PletcherSenior DBACommented:
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
 
zachvaldezAuthor Commented:
Ok I found my mistake. thanks. It only corrected a few. I think its possible that an SSN could start with '00',000'..
0
 
Patrick MatthewsCommented:
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
 
zachvaldezAuthor Commented:
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
 
Patrick MatthewsCommented:
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
 
zachvaldezAuthor Commented:
Thanks,Igot it!!
0
 
Alpesh PatelAssistant ConsultantCommented:
Just convert the Field to Text in Excel and Import it using Wizard.
0
 
zachvaldezAuthor Commented:
I didn't think. Yeah,that probably the simplest solution
0
 
zachvaldezAuthor Commented:
iT WORKED!
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

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