Need help constructing Full Names

Hi,
I have three fields that make up employees' names in the database i.e.
FirstName, MiddleInitial and LastName fields. FirstName and LastName fields
are varchar(30) and varchar(50) respectively. MiddleInitial field is char(1)
.
When I use the FirstName + ' ' + MiddleInitial + ' ' + LastName AS
EmployeeName construct, I get no data because most records have a null value
for MiddleInitial field.
If I ask for FirstName + ' ' + LastName AS EmployeeName, I get results as
desired.
How can I take data from all three fields and return EmployeeName as single
field in a stored procedure? Even if I make it work, I'll have two spaces
between FirstName and LastName if the employee has no MiddleInitial due to
null value in that field. I'm sure there's a civilized way to do this. I'd
appreciate some pointers here.
Thanks,
SamYou need to use COALESCE, ISNULL or CASE in your SELECT query:
CREATE TABLE Names
(
FirstName nvarchar(20) NOT NULL
,MiddleInitial nchar(1) NULL
,LastName nvarchar(30) NOT NULL
)
INSERT INTO Names (FirstName, MiddleInitial, LastName)
VALUES ('John', NULL , 'Doe')
INSERT INTO Names (FirstName, MiddleInitial, LastName)
VALUES ('Alfred', 'E', 'Neuman')
SELECT
FirstName + ' ' + COALESCE(MiddleInitial + ' ', '') + LastName
AS EmployeeName
FROM Names
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Sam" <Sam@.discussions.microsoft.com> wrote in message
news:2F5162E6-A0C5-47F9-8252-A5EA8243C76F@.microsoft.com...
> Hi,
> I have three fields that make up employees' names in the database i.e.
> FirstName, MiddleInitial and LastName fields. FirstName and LastName field
s
> are varchar(30) and varchar(50) respectively. MiddleInitial field is char(
1).
> When I use the FirstName + ' ' + MiddleInitial + ' ' + LastName AS
> EmployeeName construct, I get no data because most records have a null val
ue
> for MiddleInitial field.
> If I ask for FirstName + ' ' + LastName AS EmployeeName, I get results as
> desired.
> How can I take data from all three fields and return EmployeeName as singl
e
> field in a stored procedure? Even if I make it work, I'll have two spaces
> between FirstName and LastName if the employee has no MiddleInitial due to
> null value in that field. I'm sure there's a civilized way to do this. I'd
> appreciate some pointers here.
> --
> Thanks,
> Sam|||Great... Thank you very much Tibor.
--
Thanks,
Sam
"Tibor Karaszi" wrote:

> You need to use COALESCE, ISNULL or CASE in your SELECT query:
> CREATE TABLE Names
> (
> FirstName nvarchar(20) NOT NULL
> ,MiddleInitial nchar(1) NULL
> ,LastName nvarchar(30) NOT NULL
> )
> INSERT INTO Names (FirstName, MiddleInitial, LastName)
> VALUES ('John', NULL , 'Doe')
> INSERT INTO Names (FirstName, MiddleInitial, LastName)
> VALUES ('Alfred', 'E', 'Neuman')
> SELECT
> FirstName + ' ' + COALESCE(MiddleInitial + ' ', '') + LastName
> AS EmployeeName
> FROM Names
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Sam" <Sam@.discussions.microsoft.com> wrote in message
> news:2F5162E6-A0C5-47F9-8252-A5EA8243C76F@.microsoft.com...
>|||Also you might want to have a look at concat_null_yields_null parameter
in BOLsql