Combine varchar column with int column

Question

I have two columns in a SQL table, fooId (int) and fooName (varchar).

Is there a way to select them both as one column with a space between them?

select fooId + ' ' + fooName as fooEntity
from mytable

They're different types so I'm getting an error.

This field will be databound directly in a control in the web app.

SQL Server 2008

(I'm a bit of a sql beginner)

Answer 1

String concatenation is different between databases, so it helps to know which database because you need to know:

1. The concatenation method/operator
2. If the database handles implicit data type conversion

SQL Server doesn't do implicit conversion of numeric into string values:

SELECT CAST(fooid AS VARCHAR(10)) + ' ' + fooname

...so you need to use CAST (or CONVERT) to explicitly change the data type to a text based data type.

For Oracle & PostgreSQL, use the double pipe to concatenate strings:

SELECT fooid || ' ' || fooname

For MySQL, you can use the CONCAT function:

SELECT CONCAT(fooid, ' ', fooname)

Answer 2

Try this:

SELECT Convert( foold, SQL_CHAR ) + ' ' + fooName FROM mytable

or

SELECT Cast( foold AS SQL_CHAR(10) ) + ' ' + fooName FROM mytable

Answer 3

I'm not sure if it's standard SQL, but PostgreSQL uses the || operator for string concatenation. + means numerical addition.

Answer 4

I use this query in MS SQL

SELECT varcharColumn + CONVERT(VARCHAR(2), intColumn) AS foo

Assuming varcharColumn is null

SELECT ISNULL(varcharColumn, '') + CONVERT(VARCHAR(2), intColumn) AS foo

Answer 5

Yeah that should be OK, as long as the bound field is a string.

Whats the error you getting?