format ssn

Question

Post reply

format ssn

barsuk

SSChampion

Points: 12280
More actions
April 22, 2004 at 9:20 am

#161373

Hello All,
I getting SSN (hundreds of thousand of rows) from various sources,
so there are many caseses when SSN could be:
111-11-1111   --which is correct    and
11111-1111    --which is Incorrect and
111111111     --which is Incorrect and
111-111111    --which is Incorrect and
111 11 1111 --which is Incorrect
At the end of the day I need to format SSN that way, so all SSN would have either only 9 charachters
like 111111111 or 11 charachters like 111-11-1111 ( which is even better!).
I use SQL 7.0., so I cannot really use UDF.
TIA

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

fhanlon SSChampion Points: 13131 More actions · Answer 1

For a format of format of nnn-nn-nnnn how about:

EXEC sp_addtype SSN, 'VARCHAR(11)', 'NOT NULL'

GO

CREATE TABLE ShowSSNUsage

(EmployeeID INT PRIMARY KEY,

EmployeeSSN SSN,

CONSTRAINT CheckSSN CHECK ( EmployeeSSN LIKE

'[0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9][0-9][0-9]' )

)

GO

Francis

AndrewMurphy SSCertifiable Points: 5586 More actions · Answer 2

That'll prevent future incorrect data.....Is the questioner not asking how to clean existing data?

select replace(replace(ssncol,'-',''),' ','') from ssntable

once it is cleaned....then it's just a case of parsing the result above into the required display formt.

tymberwyld SSCertifiable Points: 7810 More actions · Answer 3

Ok, this is simple. Create a Stored Proc for saving data into that Table. In that Proc, Clean the data!!! I ALWAYS create a Stored Proc for Saving and Deleting, and use Functions for retrieving complicated data. The fact that Stored Procs are used for "Business" Logic makes them the ideal location for complicated code...NOT IN YOUR CLIENT CODE!

Also, if you can, make your Client Code smarter. Forexample, use a MaskedEdit Box instead of a TextBox on the User Interfaces. That way you're absolutely sure that the data is formatted correctly. If you can't, fall back to Stored Procs for Saving again!

Create Procedure spSaveData
(
@SSN VarChar(25)
)
AS
Set @SSN = Replace(@SSN, ' ', '')
Set @SSN = Replace(@SSN, '-', '')
If (Len(@SSN) < 9)
BEGIN
RAISERROR ('The SSN Field is invalid!', 16, 1) END
Set @SSN = Left(@SSN, 3) + '-' + SubString(@SSN, 4, 2) + '-' + SubString(@SSN, 6, 4)
-- Code to Save Data into Table

ericp37 SSChasing Mays Points: 631 More actions · Answer 4

The above suggestions are good, but it is is an existing application, after you cleanup the data, make the cleanup a trigger. Regardless of how ever it comes in, it will be cleaned.

tymberwyld SSCertifiable Points: 7810 More actions · Answer 5

Ok, ericp37 has a point. If this is an existing app, you can create a Trigger very similar to what I suggested in the Stored proc, or I believe you can have the Trigger call a Stored Proc. Either way...but I suggest everything in the Trigger.