group_concat in MySQL

Murali · #41 (**permalink**) 10-29-2007, 02:33 AM

Hi All,

Code:

CREATE TABLE DUMMY(ID INTEGER,X LONGTEXT);
INSERT INTO DUMMY VALUES (1,1);
COMMIT;

Code:

SELECT LENGTH('123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT')AS Len;

Output Length: 1457

Code:

SELECT GROUP_CONCAT('123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT') FROM DUMMY AS Result;

Output Result Length: 1024

But my String has the Length of 1457 and iam getting the length of just 1024.
What may be the reason?

Now i checked the Variable group_concat_max_len in my server side and i found that,

Code:

SELECT @@group_concat_max_len;

Code:

Output: 1024

So i really need to adjust my Group Concat Maximum Length to Get more Values in the String.. Here i do as per to Adjust the Length in the Session as,

Code:

SET SESSION group_concat_max_len:=4294967295; -- 4 GB size;

Now I tried the Same query,

Code:

SELECT GROUP_CONCAT('123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT') FROM DUMMY AS Result;

Output Result Length: 1457

Hope it is clear !!!!

Gopisoft · #42 (**permalink**) 11-08-2007, 10:00 PM

Hi,

CONCAT() and CONCAT_WS() function

CONCAT() Function

Code:

Syntax
CONCAT(str1,str2,...)

This function eeturns the string that results from concatenating the arguments. May have one or more arguments. If all arguments are non-binary strings, the result is a non-binary string. If the arguments include any binary strings, the result is a binary string. A numeric argument is converted to its equivalent binary string.

CONCAT() returns NULL if any argument is NULL.

Example:

Code:

SELECT CONCAT(CAST(int_col AS CHAR), char_col);

mysql> SELECT CONCAT('My', 'S', 'QL');
        -> 'MySQL'
mysql> SELECT CONCAT('My', NULL, 'QL');        
        -> NULL
mysql> SELECT CONCAT(14.3);
        -> '14.3'

CONCAT_WS() Function

Code:

Syntax
CONCAT_WS(separator,str1,str2,...)

CONCAT_WS() function for Concatenate With Separator and is a special form of CONCAT(). The first argument is the separator for the rest of the arguments. The separator is added between the strings to be concatenated. The separator can be a string, as can the rest of the arguments. If the separator is NULL, the result is NULL.

Note: CONCAT_WS() does not skip empty strings. However, it does skip any NULL values after the separator argument.

Code:

mysql> SELECT CONCAT_WS(',','First name','Second name','Last Name');
        -> 'First name,Second name,Last Name'
mysql> SELECT CONCAT_WS(',','First name',NULL,'Last Name');
        -> 'First name,Last Name'

Thanks
R.Gopi.

Murali · #43 (**permalink**) 03-31-2008, 08:00 PM

Quote:

Originally Posted by Murali

Hi All,

Code:

CREATE TABLE DUMMY(ID INTEGER,X LONGTEXT);
INSERT INTO DUMMY VALUES (1,1);
COMMIT;

Code:

SELECT LENGTH('123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT')AS Len;

Output Length: 1457

Code:

SELECT GROUP_CONCAT('123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT') FROM DUMMY AS Result;

Output Result Length: 1024

But my String has the Length of 1457 and iam getting the length of just 1024.
What may be the reason?

Now i checked the Variable group_concat_max_len in my server side and i found that,

Code:

SELECT @@group_concat_max_len;

Code:

Output: 1024

So i really need to adjust my Group Concat Maximum Length to Get more Values in the String.. Here i do as per to Adjust the Length in the Session as,

Code:

SET SESSION group_concat_max_len:=4294967295; -- 4 GB size;

Now I tried the Same query,

Code:

SELECT GROUP_CONCAT('123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT,123PRINT,245PRINT,456PRINT') FROM DUMMY AS Result;

Output Result Length: 1457

Hope it is clear !!!!

group_concat_max_len is a system Variable which can be modified in every session using the syntax,

Code:

SET SESSION group_concat_max_len:=4294967295; -- 4 GB size;

When u need to make it fixed permanently then u can assign the value to the system variable in your MYSQL config file (my.cnf)

Just add as follows in the MYSQL config file,

Code:

group_concat_max_len:=4294967295;

Now u need not to set the variable value in every session and you can simply work with that once u have set permanently in the MYSQL config file.

levelup · #44 (**permalink**) 09-29-2008, 05:56 AM

Thanks for all your post, I have the same problem and now it has been sorted

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

LinkBacks (?) LinkBack to this Thread: http://www.discussweb.com/database-support/1401-group_concat-mysql.html
Posted By	For	Type	Date
Group_Concat in MYSQL: Group_Concat in MYSQL	This thread	Refback	11-09-2007 04:40 AM
distinct with multiple field - MySQL Forum	This thread	Refback	09-26-2007 02:52 AM
Group_Concat in MYSQL	This thread	Refback	09-12-2007 12:09 AM
distinct with multiple field - MySQL Forum	This thread	Refback	08-25-2007 02:07 AM
distinct with multiple field - MySQL Forum	This thread	Refback	08-24-2007 07:17 AM
DiscussWeb IT Community - Fusing	This thread	Refback	08-01-2007 07:52 AM

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
multiple group_concat problem	fncll	Database Support	4	11-30-2007 12:06 AM