How to set a function as a default value in SQL Server?

0
votes

I am in trouble please help.

After searching for my question I decided to post here.

Look at my code below and tell me if I can set default value as a function.

What I want to do is to show first three characters of my first column's data which is Product_Name.

For example my first column has a name Xperia and and I want its first three characters to be shown in the last column which I have created using alter code.

I hope you understand what I say.

Xperia ---->  Xpe

Code is :

alter table dummy
add new_col varchar(250) default substring(first_column, 1, 3)

returns an error:

Msg 128, Level 15, State 1, Line 2
The name "first_column" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.

However I can set getdate() function as a default value.

Thanks

1
sql-serverdefault
I don't understand the last part of your question. You say "How ever i can set getdate() function as a default value" but I cannot see how that relates to adding a field based on Product_Name? – Tony
oh that was an example of setting a function as a default value.. it has nothing to do with this code.. sorry if you misled – user3991213

1 Answers

2
votes

Why are you trying to alter the table? It sounds like you need a VIEW.

A view can add an additional field, selecting only the first three characters, and would not need to be updated each time you UPDATE or INSERT data. For example:

CREATE VIEW Table_With_Truncated_Product_Name
AS
SELECT *, LEFT(Product_Name, 3) AS truncated_product_name
FROM Your_Table

However, if you don't want to use a VIEW it may be possible to use a Computed Column.

In which case you would alter your table and add the field:

ALTER TABLE Your_Table ADD truncated_product_name AS LEFT(Product_Name, 3)

Although I don't have access to SQL Server at the moment to check that would work.