I want to join few attributes in select statement as one for example
select id, (name + ' ' + surname + ' ' + age) as info from users
this doesn't work, how to do it? I'm using postgreSQL.
views:
146answers:
9
+2
A:
If you're using mysql or oracle then try CONCAT function:
SELECT id, CONCAT(name, ' ', surname, ' ', age) as info FROM users
Ivan Nevostruev
2009-11-12 17:18:40
This works in oracle as well.
David Oneill
2009-11-12 17:23:08
+2
A:
It perfectly might be dependent of the database I would take a look for a concatenation function for the database you are running the select for.
Example. Mysql: CONCAT(name, surname, age).
pedromarce
2009-11-12 17:19:43
+1
A:
That should work as is, but in general it is better not to do too much concatenation sql side if you can help it; rather return all the columns and concat them on the other end.
What are the data types you are using? You may need to CAST / CONVERT into (n)varchar
Nick
2009-11-12 17:20:11
I disagree. I prefer to have as much manipulation done inside the result set rather than adding code to the application.
Larry Lustig
2009-11-12 17:21:27
@Larry - doesn't that kind of lock you into the concatenated data? What if I decide I want to list things as Surname, Name (Age). Now I have to either write a new query/sproc or parse the string. For me the database is mainly for data storage and access and very little data manipulation.
Nick
2009-11-12 17:29:52
@Nick: Yes, but the alternative is that you need to concatenate the strings in every codebase connecting to the database.
OMG Ponies
2009-11-12 18:08:58
+2
A:
You may need to cast the fields to a common type before concatenating. In T-SQL for example this would read.
Select id, Cast(name as varchar(50)) + ' ' + Cast(surname as varchar(50)) + ' ' +
Cast(age as varchar(3)) As info From Users
Steve Homer
2009-11-12 17:20:27
+2
A:
|| is used for this purpose.
Use it like select name ||' ' ||surname ||' ' ||age as info from users
Sachin Chourasiya
2009-11-12 17:21:36
+1
A:
Make sure your data types are similar and convert any datatypes to string as necessary:
select id, (name + ' ' + surname + ' ' + convert(varchar(3),age)) as info from users
Blake Blackwell
2009-11-12 17:21:43
+3
A:
I believe the ANSI standard concatenation operator is: ||
SELECT id, name ||' ' || surname || ' ' || age AS info FROM users
ninesided
2009-11-12 17:22:27
:) I am writing the answer while you have posted yours one. You can see the difference of 50 sec.
Sachin Chourasiya
2009-11-12 17:23:38
+1
A:
Works fine in most databases I know, although you probably have to CAST the age field to be TEXT. The exact method for doing this depends on the database you're using, which you did not specify.
Larry Lustig
2009-11-12 17:22:49
Ugh, that's another reason why I like Oracle more than SQL Server - implicit datatype conversion.
OMG Ponies
2009-11-12 18:10:15
+2
A:
Postgres uses || to concatenate strings; your query needs to be:
select id, (name || ' ' || surname || ' ' || age) as info from users
It should be the key just above the Enter key on a full keyboard, but on mine it's not a sold line - there's a break in the middle of it. You have to hold the Shift key to get the character (called a pipe, btw).
OMG Ponies
2009-11-12 17:28:59