Create computed column with string in formula

Question

I want to create a computed column in SQL SERVER and set the formula to this

([Category] +  '.aspx?ID=' + [Post_ID])

Not working though......what am i missing?

Category and Post_ID are current columns in the table

abatishchev · Answer 1 · 2010-10-23T10:44:46.303

Do you do

SELECT [Category] +  '.aspx?ID=' + [Post_ID]
FROM table

?

Try

SELECT CAST([Category] AS NVARCHAR(MAX)) + '.aspx?ID=' CAST([Post_ID] AS NVARCHAR(MAX))
FROM table

or specify max size depending on your columns' data type.

Above answer is bad. DON'T FORMAT ON DATA LAYER. FORMAT ON PRESENTATION LAYER, i.e. in mark-up. E.g.:

<asp:HyperLinkField
    HeaderText="LinkHeader"
    DataNavigateUrlFormatString="{0}.aspx?ID={1}" 
    DataNavigateUrlFields="Category,Post_ID"
    DataTextField="LinkName" />

(to work properly this requires also a field LinkName to exists in the resulting selection)

or

<asp:Hyperlink runat= "server"
    Text='<%# DataBinder.Eval(Container.DataItem, "LinkName").ToString() %>' 
    NavigateUrl='<%# DataBinder.Eval(Container.DataItem, "Category").ToString() + ".aspx?ID=" + DataBinder.Eval(Container.DataItem, "Post_ID").ToString() />

How would I do it on this: NavigateUrl='<%#Eval("Post_ID", ".aspx?ID={0}")%>'> — Etienne, Oct 23 '10 at 10:32
@Etienne: See http://stackoverflow.com/questions/1779481/hyperlink-with-navigateurl-with-eval-where-is-a-mistake — abatishchev, Oct 23 '10 at 10:39

score 1 · Answer 2 · answered Oct 23 '10 at 10:37

1

I guess you're probably missing a cast. I agree that this seems an unlikely candidate for a computed column though.

create table #t
(
Category varchar(50),
[Post_ID] int
)

alter table #t 
add comp as ([Category] +  '.aspx?ID=' + cast([Post_ID] as varchar(10)))

answered Oct 23 '10 at 10:37

Martin Smith

438,706
87
741
845

1

I think it's bad idea to create such computed column and much better - just format data on-the-fly on the presentation layer – abatishchev Oct 23 '10 at 10:40
1

@abatishchev I can't think of any circumstances where this column would be a good idea either. – Martin Smith Oct 23 '10 at 10:43

score 0 · Accepted Answer · answered Oct 23 '10 at 10:57

0

This actually sorted it out for me at the end.....

NavigateUrl='<%# String.Format("{0}.aspx?ID={1}", DataBinder.Eval(Container.DataItem, "Category"), DataBinder.Eval(Container.DataItem, "Post_ID")) %>'

answered Oct 23 '10 at 10:57

Etienne

7,141
42
108
160

Create computed column with string in formula

3 Answers3