Equivalent of implode() purely in MySQL

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
Chris Corbyn
Breakbeat Nuttzer
Posts: 13098
Joined: Wed Mar 24, 2004 7:57 am
Location: Melbourne, Australia

Equivalent of implode() purely in MySQL

Post by Chris Corbyn »

Hi,

I'm looking to pull data (just one column) from mysql which is over multiple rows.

e.g.

apple
banana
orange
pear

Now, I actually want the data in this format in just one row:

"apple,banana,orange,pear"

Does mysql offer some sort of "SELECT IMPLODE(',', my_column) AS foo" type of thing? I know I can do it with PHP but this a part of a much larger query so I'm trying to keep this all in a single query.

Cheers,

d11
User avatar
JayBird
Admin
Posts: 4524
Joined: Wed Aug 13, 2003 7:02 am
Location: York, UK
Contact:

Post by JayBird »

CONCAT_WS() maybe?
User avatar
Chris Corbyn
Breakbeat Nuttzer
Posts: 13098
Joined: Wed Mar 24, 2004 7:57 am
Location: Melbourne, Australia

Post by Chris Corbyn »

Pimptastic wrote:CONCAT_WS() maybe?
I tried but it just sticks it with the column from one row only. I then tried:

Code: Select all

select concat_ws(', ', (select column_name from tbl_name)) as joined_string
But that gives an error that concat_ws() can't contain more than one row.

I've approached the problem from a different angle anyway and still got it in one query but I'll need to use PHP to implode thos columns.

If anybody happens to know the MySQL way to do it, it would be cool though, although I won't use it in this instance now.

Where's ~timvw? :)
User avatar
JayBird
Admin
Posts: 4524
Joined: Wed Aug 13, 2003 7:02 am
Location: York, UK
Contact:

Post by JayBird »

Im just throwing things at you here now but what does GROUP_CONCAT do?

http://dev.mysql.com/doc/refman/5.0/en/ ... tions.html
User avatar
Chris Corbyn
Breakbeat Nuttzer
Posts: 13098
Joined: Wed Mar 24, 2004 7:57 am
Location: Melbourne, Australia

Post by Chris Corbyn »

FYI:

Code: Select all

select group_concat(column_name) from tbl_name
Yeilds a comma delimited string :)
User avatar
Benjamin
Site Administrator
Posts: 6935
Joined: Sun May 19, 2002 10:24 pm

Post by Benjamin »

Maybe

Code: Select all

serialize()
would work for you when saving it, and unserialize() when pulling it back out.
Post Reply