I have a primary key ID column I want to convert to a string, any type of string. Now, I know how to do this for a specific value, by using either CAST or CONVERT. But how do I go about doing it for the whole field? I guess we use some type of Transact SQL , but I would appreciate some pointers on how to do this. Thanks.

  • 1
    Welcome to DBA stack exchange, may you please provide more details. thanks – Ahmad Abuhasna Oct 30 '16 at 11:19
  • 1
    Are you trying to change the datatype of the field in the table to a character type? – mendosi Oct 31 '16 at 0:56
  • @mendosi: precisely. – MSIS Oct 31 '16 at 15:18
  • @AhmadAbuhasna: Please see my reply to mendosi. – MSIS Oct 31 '16 at 15:18

I'm not sure what you mean by 'whole field", but this is how you'd convert an Integer to a varchar string. The 'second' select proves it's a string because you can concatenate brackets around it.

You'd apply the same technique when selecting rows from a table.

DECLARE @IntColumn int = 5
select convert(varchar(20),@IntColumn)
select '[' + convert(varchar(20),@IntColumn) + ']'

Changing the data type of a field used in the primary key of a table is a task not to be entered into lightly!

First you would need to drop the primary key constraint, then change the data type, then add a new primary key constraint. This will surely result in an outage and probably lots of other potential gotchas, so you'll need to think through all those implications.

Alter Table my_table Drop Constraint pk_my_table;
Alter Table my_table Alter Column ID varchar(11) Not Null;
Alter Table my_table Add Constraint pk_my_table Primary Key Clustered (ID);

(Note, this assumes that you don't have IDENTITY specified for the column. If you do then see https://stackoverflow.com/questions/8230257/remove-identity-from-a-column-in-a-table).

An alternative which may meet your needs is to add a computed column to the table (or to a view over the table) that contains the same value as the ID field but converted to a character type. This is less likely to interfere with other things, but still might cause trouble if there are SELECT * FROM my_table statements being used in applications.

Alter Table my_table Add ID_string As Convert(varchar(11), ID);
  • How about copying the t-sql code, dropping the old table and creating a new one with the new data type? I guess we need to check out the constraints. Does SQL automatically check the constraints, or are the constraints all parts of the DDL? Or maybe we can cut the whole database, modify the code and then create a new database with the old code? I just worry about whether the old data values inserted will be "orphaned" without a logical table? Sorry I can't upvote your comment, I don't have enough points. – MSIS Nov 6 '16 at 22:33
  • @MSIS I'm sure those approaches you suggested would work, but they might be more effort, and more disruption, than the steps I described. – mendosi Nov 6 '16 at 23:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.