SQL Alter statement

April 6, 2011 6:56 AM

SQL SERVER: Need help creating new column in a table with unformatted SSN.

Some of the SSN's in a database are written with dashes, XXX-XX-XXXX. In other tables the SSN's are just XXXXXXXXX.

I'd like to create a new column in the tables where the SSN is formatted with dashes that replaces the the - with nothing, so that it is formatted as XXXXXXXXX.

Any help with this statement, i thought it was:
ALTER TABLE tablename
ADD columnName AS REPLACE(SSN,"-","")

Let me know what you think

posted by fozzie33 to computers & internet (6 answers total)

I haven't seen this done as a single SQL statement.
I'd use:

ALTER TABLE `tablename` ADD `columnName` VARCHAR (100) or whatever...;
UPDATE `tablename` SET `columnName` = REPLACE(SSN,"-","");
posted by le morte de bea arthur at 7:04 AM on April 6, 2011

i created the column... now trying to do the update....

got this error

Msg 1038, Level 15, State 4, Line 1
An object or column name is missing or empty. For SELECT INTO statements, verify each column has a name. For other statements, look for empty alias names. Aliases defined as "" or [] are not allowed. Change the alias to a valid name.
posted by fozzie33 at 7:08 AM on April 6, 2011

nevermind... it's a '-','' not a " -",""
posted by fozzie33 at 7:12 AM on April 6, 2011

Just as a tangential question, what purpose do you have in storing SSNs? Generally, this is a pretty big nono...
posted by jangie at 7:17 AM on April 6, 2011

i mefi mailed you....
posted by fozzie33 at 7:35 AM on April 6, 2011

What I would do here is use a calculated column with persisted values. This means you are not responsible for inserting/updating the data in the new column, the server will do it for you, and persist the data to disk. This gives you the best of both worlds - you do not have to maintain the column, and you can still index it for performance.

See Using Computed Columns in SQL Server with Persisted Values
posted by SNACKeR at 9:01 AM on April 6, 2011