How to create a calculated field? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

How to create a calculated field?

Author

Topic

nbourre
Starting Member

14 Posts

Posted - 2008-07-25 : 09:32:00

Hi,

I'm a newbie in SQL development. I'm from the Filemaker world. So it's a bit confusing.

I would like to concatenate two field (fieldA, fieldB) in a third field (fieldC) all in the same table.

The concatenation goes like this

fieldC = [fieldA] & "_" & [fieldC]

I have seen a formula property in the SQL server manager. Is that the place?

Thank you

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-07-25 : 09:39:46

Select fieldA,fieldB,fieldA+fieldB as fieldC from table

Madhivanan

Failing to plan is Planning to fail

VGuyz
Posting Yak Master

121 Posts

Posted - 2008-07-25 : 09:41:14

DO you want to get the third colum
like the below means,use the following,

Third_coumn =First_column-second_column

select (First_column+'-'+second_column) as third_column
from table_name.

nbourre
Starting Member

14 Posts

Posted - 2008-07-25 : 09:46:59

Actually, I want to store the calculation in the same table, so I could use the result as a lookup for later use.

Nick
Beginning with something is a good start!

VGuyz
Posting Yak Master

121 Posts

Posted - 2008-07-25 : 09:58:54

can you tell me clearly..
How you want to have the column.
DO you want the column In the table itself

or

You want to get the third column as i mentioned in the previous post.
ie., in the query.

nbourre
Starting Member

14 Posts

Posted - 2008-07-25 : 10:13:18

I want a third column in the table.

I think I figured it out by myself here's my code:

CREATE TABLE [dbo].[estate]
(
[pkEstateID] [int] IDENTITY [1,1] NOT NULL,
[fkStreetID] [int],
[fkCityID] [int],
[cityStreet] as (CONVERT([nvarchar](10),[fkCityID],0) + '_' + CONVERT([nvarchar](10),[fkStreetID],0))
)

Is that right?

Nick
Beginning with something is a good start!

VGuyz
Posting Yak Master

121 Posts

Posted - 2008-07-25 : 10:25:55

Ya its correct,
don't use '[]'this symbols for datatype.
use the set brackets to represent the column alone.

CREATE TABLE [dbo].[estate]
(
[pkEstateID] int IDENTITY (1,1) NOT NULL,
[fkStreetID] int,
[fkCityID] int,
[cityStreet] as (CONVERT(nvarchar(10),[fkCityID],0) + '-' + CONVERT(nvarchar(10),[fkStreetID],0))
)

select * from estate

insert into estate(fkStreetID,fkCityID)values(11,22)

select * from estate

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-25 : 10:33:56

make sure you account for NULL values also just in case the two source fields are nullable.

..
[cityStreet] as (COALESCE(CONVERT(nvarchar(10),[fkCityID],0) + '-','') + COALESCE(CONVERT(nvarchar(10),[fkStreetID],0),''))
..

nbourre
Starting Member

14 Posts

Posted - 2008-07-25 : 13:35:14

Ok thank for your help!

Nick
Beginning with something is a good start!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources