[thelist] a Monday Freebie

Scott Dexter sgd at ti3.com
Mon Jul 31 17:56:29 CDT 2000


since I figured out something I thought was kinda cool I'm passing it along:

<tip type="MS SQL Server">
You're storing numbers in the database as, well, numbers. That's good. Thing
is, when you store Social Security (or, Social Insurance for our Canadian
neighbors) Numbers, the leading zero(s) get lopped off. So when you go and
pull them out, the zero(s) are gone.

You could go and write a function in the client process to handle it and
prepend them back on, but why do that when you can have SQL Server do it for
you?

SQL Server 7 has a function called REPLICATE (I don't know if the function
is part of ANSI SQL or not, check your documentation to be sure before
trying it on Oracle) which repeats a character string a definable number of
times. You can use it to add as many zeros as you want:

Select 'SSN' = REPLICATE('0',9-Len(ssn)) + cast(ssn as varchar(9)) from
tbUsers

In the select statement above, I'm calculating how many zeros I need by
subtracting the length of the SSN (in characters) from nine (SSNs are nine
digits in length). I have to concatenate it to a character casted version of
the SSN to get the data types happy.

Now 7 and 8 digit SSNs will get spit out with their leading zero(s), as they
should. Nine digit SSNs get zero zeros prepended to it.
</tip>

sgd
--
think safely




More information about the thelist mailing list