Set the default value of a column based on another column of a different data type

Question

I'm creating a new table in SQL table designer, and I'd like the "Default Value or Binding" of the column to be based off the value of another column in the same table. So I'd like Column A to get it's value from a substring of Column B. Column A is a numeric data type, Column B is varchar. Right now I have the following, but get the message in screenshot 2...is there something wrong here? I also had the expression CONVERT(numeric (2,0), SUBSTRING(col_b,3,2)) and that gave the same message.

Screenshot 1:

Screenshot 2:

Answer 1

I don't think you can set the DEFAULT value of one column based on another column using the DEFAULT CONSTRAINT.

You may want to consider a computed column if all you're looking to do is replace a NULL value. http://msdn.microsoft.com/en-us/library/ms188300.aspx

In extreme cases, you can do exactly what you want in a TRIGGER. Read up on INSTEAD OF INSERT triggers. http://technet.microsoft.com/en-us/library/ms175089(v=sql.105).aspx