SQL alias problem

[Albion]

I have a table with three fields, first_name, middle_name, Last_name. I'd like to create a query that will combine those three into one alias. I know I am just missing the operator but I've searched the web, usenet and here and can't find anything (Searching on 'AS' is a bit difficult)

This is what I've tried:

SELECT tblemp.First_Name + tblemp.Middle_Name + tblemp.Last_Name AS FullName
FROM tblemp

But that doesn't work, it returns 0. I also tried replacing the + with & or AND but those didn't work either.

thanks

-al

 

[rac2]

What brand of database are you using?

What are the datatypes of the three columns?

Do you have NULL for any of the names?

SELECT tblemp.First_Name + tblemp.Middle_Name + tblemp.Last_Name AS FullName
FROM tblemp

This works in MS SQL Server when the columns are VARCHAR or CHAR. But it results in NULL if one of the names is NULL.

And it is good style to use quote marks around aliases.

Maybe put some spaces between the names, too.

SELECT
      ISNULL(tblemp.First_Name, '') + ' ' +
      ISNULL(tblemp.Middle_Name, '') + ' ' +
      ISNULL(tblemp.Last_Name, '') AS "FullName"
FROM tblemp

 

[Albion]

In MySQL using + results in 0. I solved the problem by using the function concat(). It took me forever to find it though, none of the references out there list the functions avaliable in SQL except for sum, avg, and count.

Is there a free generic SQL reference somewhere?

-al

 

[swampBoogie]

A generic SQL reference would not help since concat is specific to Mysql. Besides, Mysql lacks a lot of standard sql features.

Look at the online documentation at

http://www.mysql.com

 

[abombss]

When on Oracle I always have to enclose the column in double quotes if I use the AS, but without the AS it works just fine.

SELECT tblemp.First_Name + tblemp.Middle_Name + tblemp.Last_Name AS "FullName"
FROM tblemp

OR

SELECT tblemp.First_Name + tblemp.Middle_Name + tblemp.Last_Name FullName
FROM tblemp

The latter version won't work if there are spaces in the name.

abombss

 

[sbhave]

In DB2 it is done as follows

select first_name || middle_name || last_name as NAME from temp.table


If an integer value is to be concatenated we have to use cast() function for converting integer to character