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