join several fields to create new field

What would be the recommendation/approach in creating a seperate field in which joins several differate fields together.

I have a table with field name a, b, and c. I want the information in those fields to be populated in a seperate field, d.

So instead of:

a

122

b

joe

c

st

I would have:

d

122 joe st

Thanks!

You need to separate the storage of data from it's presentation. Generally you would store data at the lower level, then combine it when you retrieve it. You can do that either in the application that retrieves it, or in SQL. For instance in your example:

Select a + ' ' + b + ' ' + c as d

would give the result you are after

|||Could this be accomplished in a sql view?|||It could, but in my view you're still using the database to process data rather than storing it. You could also use a stored procedure|||I should clarify myself. I want the attribute data in fields

a,b,c to popluate a new field d. I've created field d in the

table with fields a,b,c. Now I just need to populate it.

Thanks.|||Possibley a better way of doing this is in the view because I

don't need to store the actual data. I just need a way to present

it in an application. So if there's a sql statement that I could

add to my existing view, that would work as well.

One caveaet in the current view is that I still want the a,b,c fields to present themselves in the view.

So ...

select a, b, c, d (as the virtual field which concatenates a,b,c)

from f

the presentation would be:

a b c d

123 joe st 123 joe st|||

The field 'd' is totally superfluous. There is no need to duplicate the data in storage.

Select a, b, c, a + ' ' + b + ' ' + c as d

Will produce exactly the output you want. You can put this select either in a stored proc, a view or in raw sql, it doesn't matter.

|||This works ... sort of.

If there are any null fields, it returns nothing. So is there a way around that.

Something can be built into the view that handles: if null then '' otherwise d + ' ' ?|||I've worked with the view and it is working well enough to return valid

results. I can't use it for the application however. I need

to actually store the data in a seperate field.

If there are thoughts on how to make this happen, I would appreciate it.

The problems I see are:

the new field must have spaces - example 122 joe st

there may be a direction but not in all cases - example 122 N joe st

Thanks.|||Depending on how complex you want your logic to be, you could either use Computed Columns (for simple logic. See http://msdn2.microsoft.com/en-us/library/ms191250.aspx) or DML triggers (for more complex logic. See http://msdn2.microsoft.com/en-us/library/ms191524.aspx).