SQL Alter statement
April 6, 2011 6:56 AM Subscribe
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
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
Response by poster: 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
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
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
posted by jangie at 7:17 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
See Using Computed Columns in SQL Server with Persisted Values
posted by SNACKeR at 9:01 AM on April 6, 2011
This thread is closed to new comments.
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