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 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?
>
>