devxlogo

Concatening Columns Using AS

Concatening Columns Using AS

Question:
I’m trying to retrieve the “firstname” and “lastname” fields from a SQL Server database, place them into a column called “fullname” using the AS statement. When I simply retrieve “lastname,” it works just fine. As soon as I do the concatenation, I get an error message from the SQL server with regard to the datatypes (mismatch of boolean and char). I’ve checked the datatypes of all fields in the SQL server and they are all char.

Here is my code:

Set oRS = oConn.Execute("SELECT FirstName &
' ' & LastName AS FullName, E_Mail, SchoolId from
Member2 where SchoolId='" & strSchoolId & "'")While Not oRs.EOF%>

Answer:
Unlike VB, SQL Server doesn't recognize the ampersand (&) as a valid concatenation operator. It's actually a bitwise AND for integer or binary datatypes. Use the plus sign (+) instead. For example, this query in the Pubs database fails with the error message you mentioned:

SELECT au_lname & au_fname AS Name FROM authorsBut this statement works:SELECT au_lname + au_fname AS Name FROM authors

devxblackblue

About Our Editorial Process

At DevX, we’re dedicated to tech entrepreneurship. Our team closely follows industry shifts, new products, AI breakthroughs, technology trends, and funding announcements. Articles undergo thorough editing to ensure accuracy and clarity, reflecting DevX’s style and supporting entrepreneurs in the tech sphere.

See our full editorial policy.

About Our Journalist