"Sam" <S**@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 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,
>
> 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" <S**@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 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,
> >
> > Sam
>
>