Combinig fields

Hi all,
In Access I can do a simply query and put something like:
SELECT FirstName & ", " & LastName AS FullName FROM People
doing this woud combine the two fields into one... can this be done in a SQL
stored procedure?
Thanks
GavUse the + operator to combine columns...just make sure that you don't =
add numbers if you really want to combine them as strings. =20
use pubs
go
select au_lname, au_fname, 'FullName' =3D au_lname + ', ' + au_fname =
From authors
--=20
Keith
"Gav" <spam@.spam.com> wrote in message =
news:e3jEGHECEHA.2348@.TK2MSFTNGP09.phx.gbl...
> Hi all,
>=20
> In Access I can do a simply query and put something like:
>=20
> SELECT FirstName & ", " & LastName AS FullName FROM People
>=20
> doing this woud combine the two fields into one... can this be done in =
a SQL
> stored procedure?
>=20
> Thanks
> Gav
>=20
>|||I have tried this but it simply returns null all the time.
Regards
Gav
"Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
news:u2yfqNECEHA.2308@.tk2msftngp13.phx.gbl...
Use the + operator to combine columns...just make sure that you don't add
numbers if you really want to combine them as strings.
use pubs
go
select au_lname, au_fname, 'FullName' = au_lname + ', ' + au_fname From
authors
Keith
"Gav" <spam@.spam.com> wrote in message
news:e3jEGHECEHA.2348@.TK2MSFTNGP09.phx.gbl...
> Hi all,
> In Access I can do a simply query and put something like:
> SELECT FirstName & ", " & LastName AS FullName FROM People
> doing this woud combine the two fields into one... can this be done in a
SQL
> stored procedure?
> Thanks
> Gav
>|||I can see whats happening, if one of the fields is null it only returns
null... can I get it to ignore the field if it is null?
Regards
Gav
"Gav" <spam@.spam.com> wrote in message
news:uT9giUECEHA.464@.TK2MSFTNGP11.phx.gbl...
> I have tried this but it simply returns null all the time.
> Regards
> Gav
> "Keith Kratochvil" <sqlguy.back2u@.comcast.net> wrote in message
> news:u2yfqNECEHA.2308@.tk2msftngp13.phx.gbl...
> Use the + operator to combine columns...just make sure that you don't add
> numbers if you really want to combine them as strings.
> use pubs
> go
> select au_lname, au_fname, 'FullName' = au_lname + ', ' + au_fname From
> authors
> --
> Keith
>
> "Gav" <spam@.spam.com> wrote in message
> news:e3jEGHECEHA.2348@.TK2MSFTNGP09.phx.gbl...
> SQL
>|||There are a few options that you can use...
Here are a few that come to mind:
CREATE TABLE #foo (col1 char(5), col2 char(5))
INSERT INTO #foo (col1, col2) VALUES ('test', null)
INSERT INTO #foo (col1, col2) VALUES ('test1', 'test1')
GO
SELECT col1 + ' ' + col2 FROM #foo=20
SELECT col1 + ' ' + ISNULL(col2, '') FROM #foo
SELECT col1 + ' ' + COALESCE(col2, '') FROM #foo
SELECT col1 + ' ' + CASE WHEN col2 IS NULL THEN '' ELSE col2 END FROM =
#foo
--=20
Keith
"Gav" <spam@.spam.com> wrote in message =
news:%23h7ZKXECEHA.3344@.tk2msftngp13.phx.gbl...
> I can see whats happening, if one of the fields is null it only =
returns
> null... can I get it to ignore the field if it is null?
>=20
> Regards
> Gav
>=20
> "Gav" <spam@.spam.com> wrote in message
> news:uT9giUECEHA.464@.TK2MSFTNGP11.phx.gbl...
don't add
From
done in a
>=20
>|||You can replace that column value with an empty string:
SELECT
au_fname + COALESCE(initial, '') + au_lname AS full_name
FROM tblname
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Gav" <spam@.spam.com> wrote in message
news:%23h7ZKXECEHA.3344@.tk2msftngp13.phx.gbl...
> I can see whats happening, if one of the fields is null it only returns
> null... can I get it to ignore the field if it is null?
> Regards
> Gav
> "Gav" <spam@.spam.com> wrote in message
> news:uT9giUECEHA.464@.TK2MSFTNGP11.phx.gbl...
add
a
>|||Thanks for the help Keith and Tibor thats works great.
Cheers
Gav
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:eU0HchECEHA.3400@.tk2msftngp13.phx.gbl...
> You can replace that column value with an empty string:
> SELECT
> au_fname + COALESCE(initial, '') + au_lname AS full_name
> FROM tblname
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
>
> "Gav" <spam@.spam.com> wrote in message
> news:%23h7ZKXECEHA.3344@.tk2msftngp13.phx.gbl...
> add
From
in
> a
>

combine to varchar field when on is null

When we combine to varchar field when on is null,
like
Select FirstName+ LastName as FullName..
if FirstName is null, then FullName will be null.
How can we combine to varchar field when on is null, and the result will be
the non-null field?Select COALESCE(FirstName, '') + COALESCE(LastName, '') as FullName
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"ad" <ad@.wfes.tcc.edu.tw> wrote in message news:OPgSvFCQFHA.3716@.TK2MSFTNGP14.phx.gbl...
> When we combine to varchar field when on is null,
> like
> Select FirstName+ LastName as FullName..
> if FirstName is null, then FullName will be null.
> How can we combine to varchar field when on is null, and the result will be
> the non-null field?
>
>|||Select ISNULL(FirstName,'')+ ISNULL(LastName,'')as FullName..
HTH, Jens Süßmeyer.
--
http://www.sqlserver2005.de
--
"ad" <ad@.wfes.tcc.edu.tw> schrieb im Newsbeitrag
news:OPgSvFCQFHA.3716@.TK2MSFTNGP14.phx.gbl...
> When we combine to varchar field when on is null,
> like
> Select FirstName+ LastName as FullName..
> if FirstName is null, then FullName will be null.
> How can we combine to varchar field when on is null, and the result will
> be
> the non-null field?
>
>

combine to varchar field when on is null

When we combine to varchar field when on is null,
like
Select FirstName+ LastName as FullName..
if FirstName is null, then FullName will be null.
How can we combine to varchar field when on is null, and the result will be
the non-null field?
Select COALESCE(FirstName, '') + COALESCE(LastName, '') as FullName
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"ad" <ad@.wfes.tcc.edu.tw> wrote in message news:OPgSvFCQFHA.3716@.TK2MSFTNGP14.phx.gbl...
> When we combine to varchar field when on is null,
> like
> Select FirstName+ LastName as FullName..
> if FirstName is null, then FullName will be null.
> How can we combine to varchar field when on is null, and the result will be
> the non-null field?
>
>
|||Select ISNULL(FirstName,'')+ ISNULL(LastName,'')as FullName..
HTH, Jens Smeyer.
http://www.sqlserver2005.de
"ad" <ad@.wfes.tcc.edu.tw> schrieb im Newsbeitrag
news:OPgSvFCQFHA.3716@.TK2MSFTNGP14.phx.gbl...
> When we combine to varchar field when on is null,
> like
> Select FirstName+ LastName as FullName..
> if FirstName is null, then FullName will be null.
> How can we combine to varchar field when on is null, and the result will
> be
> the non-null field?
>
>

combine to varchar field when on is null

When we combine to varchar field when on is null,
like
Select FirstName+ LastName as FullName..
if FirstName is null, then FullName will be null.
How can we combine to varchar field when on is null, and the result will be
the non-null field?Select COALESCE(FirstName, '') + COALESCE(LastName, '') as FullName
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"ad" <ad@.wfes.tcc.edu.tw> wrote in message news:OPgSvFCQFHA.3716@.TK2MSFTNGP14.phx.gbl...[vbc
ol=seagreen]
> When we combine to varchar field when on is null,
> like
> Select FirstName+ LastName as FullName..
> if FirstName is null, then FullName will be null.
> How can we combine to varchar field when on is null, and the result will b
e
> the non-null field?
>
>[/vbcol]|||Select ISNULL(FirstName,'')+ ISNULL(LastName,'')as FullName..
HTH, Jens Smeyer.
http://www.sqlserver2005.de
--
"ad" <ad@.wfes.tcc.edu.tw> schrieb im Newsbeitrag
news:OPgSvFCQFHA.3716@.TK2MSFTNGP14.phx.gbl...
> When we combine to varchar field when on is null,
> like
> Select FirstName+ LastName as FullName..
> if FirstName is null, then FullName will be null.
> How can we combine to varchar field when on is null, and the result will
> be
> the non-null field?
>
>

Combine Names

Hello.
A quick layout of what I am trying to do.
I have 3 fields in a table. There names are..
fname, lname and fullname.
What I would like to know is, how do I combine the first 2 fields to show
the results as the full name in the fullname field?
Is this possible. If so, can someone please explain.
Thank you so much.You can use a computed column, basically:
ALTER TABLE your_table DROP COLUMN fullname
ALTER TABLE your_table ADD fullname
AS fname + ' ' + lname
Jacco Schalkwijk
SQL Server MVP
"noixa1234" <noixa1234@.discussions.microsoft.com> wrote in message
news:300E0247-E903-44CD-A6AB-68A64D7489F5@.microsoft.com...
> Hello.
> A quick layout of what I am trying to do.
> I have 3 fields in a table. There names are..
> fname, lname and fullname.
> What I would like to know is, how do I combine the first 2 fields to show
> the results as the full name in the fullname field?
> Is this possible. If so, can someone please explain.
> Thank you so much.|||If the Fname and/or the Lname might be null, then you need to use the follow
ing
IsNull(FName+ ' ', '') + IsNull(LName, '')
If only the first name might be null then you can use
IsNull(FName+ ' ', '') + LName
Charly
"noixa1234" wrote:

> Hello.
> A quick layout of what I am trying to do.
> I have 3 fields in a table. There names are..
> fname, lname and fullname.
> What I would like to know is, how do I combine the first 2 fields to show
> the results as the full name in the fullname field?
> Is this possible. If so, can someone please explain.
> Thank you so much.|||Select FName, LName , FName + ' ' + LName as Fullname from <source table>
I assume FName and Lname are either char or varchar fields; you can simply
concatenate to create the full name.
regards,
Sarav...
"noixa1234" <noixa1234@.discussions.microsoft.com> wrote in message
news:300E0247-E903-44CD-A6AB-68A64D7489F5@.microsoft.com...
> Hello.
> A quick layout of what I am trying to do.
> I have 3 fields in a table. There names are..
> fname, lname and fullname.
> What I would like to know is, how do I combine the first 2 fields to show
> the results as the full name in the fullname field?
> Is this possible. If so, can someone please explain.
> Thank you so much.|||Here is an example:
use northwind
go
select employeeid, lastname, firstname
into dbo.t1
from dbo.employees
go
-- you do not need a computed column.
-- you can do the concatenation in a select statement
alter table dbo.t1
add fullname as lastname + ', ' + firstname
go
select * from dbo.t1
go
drop table dbo.t1
go
AMB
"noixa1234" wrote:

> Hello.
> A quick layout of what I am trying to do.
> I have 3 fields in a table. There names are..
> fname, lname and fullname.
> What I would like to know is, how do I combine the first 2 fields to show
> the results as the full name in the fullname field?
> Is this possible. If so, can someone please explain.
> Thank you so much.