How do I combine all rows into one row, while controlling the seperator?

[Poff111]

Hi phpfreaks,

I use the normal ol' MySQL 5.0

I was wondering if there's a way to gather all of a single column's row into one row.

For example:

ROW

Hi

This

Is

Me

 

What I want to do is merge them into one row with the ability to control the seperator, like:

Hi:This:Is:Me

 

I searched for an answer and only came up with "GROUP_CONCAT", but this doesn't seem to allow the control of the seperator... it always has a "," as it.

 

Regards.

[pdkv2]

Yes GROUP_CONCAT has rhe "," as a separator by default but you can manipulate the separator as follows

 

GROUP_CONCAT(clo_name SEPARATOR ":")

 

 

 

Cheers !

Sharad

[Poff111]

Hi phpfreaks,

Ah! Thanks. I was also wondering: Is it possible to do a WHERE statement to check if something is there and if it's there, it'll be replaced by ":"?

Example:

ROW

Hi

This

Is

Me

 

I want to gather all of that except for... "Is". Which should return:

Hi:This::Me

[pdkv2]

Everything is possible !

Use

 

REPLACE(GROUP_CONCAT(col_name SEPARATOR ":"),"Is","")

 

Thats all

 

Cheers !

Sharad

[Poff111]

Hi phpfreaks,

Thanks again!

But what if I wanted to replace a certain one?

Example:

ROW

Hi

This is Poff

This is Poff Again

What's up?

 

I want to replace the words "This is Poff" but NOT "This is Poff Again" to ":".

 

Regards.

[fenway]

Don't think that you can do look aheads in mysql.