How to Quote Values Using Group_Concat

How to quote values using group_concat

Use:

GROUP_CONCAT(CONCAT('''', your_column, '''' ))

How to quote values of single column using group_concat and concat, distinct

Try this, Its is working perfectly in my case:

SELECT GROUP_CONCAT( DISTINCT CONCAT("'", REPLACE(user_id, "," , "','") , "'")) as listed_id FROM users

Here is the output:

group_concat inserting double quote for empty values in mysql

I suspect the some values of user_values are empty strings (which are different than null values, that group_concat() ignores). You can work around this with a case expression within group_concat() that turns empty strings to null values, like:

SELECT 
    id,
    GROUP_CONCAT(CASE
        WHEN user_values = '' THEN NULL 
        ELSE CONCAT("'", REPLACE(user_values,",", "','"), "'")
    END) as user_val 
FROM user 
WHERE id = 3;

GROUP_CONCAT not working with IN() in MySQL

However if I place the actual values

SELECT * FROM secondary_batch WHERE id IN ('4','2','6','3','5','1')

I get the output I expect.

That's not right. Really your query looks like

SELECT * FROM secondary_batch WHERE id IN ('\'4\',\'2\',\'6\',\'3\',\'5\',\'1\'')

i.e. is not a list, it is a value of one string literal.

If you want to use GROUP_CONCAT() in subquery certainly then you must use

SELECT * 
FROM secondary_batch 
WHERE FIND_IN_SET(id, (SELECT GROUP_CONCAT(QUOTE(ID)) FROM primary_batch))

But it is excess, illogical and makes no sense.

How to write query in codeigniter for group_concat and concat with replace for table column

Your query string is not valid because this can be recognized as a string:

"SELECT GROUP_CONCAT( DISTINCT CONCAT("

and the rest does not make sense:

'", REPLACE(user_id, ",", "','") , "'")) as listed_id FROM user_data"

You should escape quotation marks when they are within quotation marks of the same kind:

$this->db->query("SELECT GROUP_CONCAT( DISTINCT CONCAT(\"'\", REPLACE(user_id,
\",\", \"','\") , \"'\")) as listed_id FROM user_data");

How can I use GROUP_CONCAT with multiple values?

I just used single quotes for string and double quote for attribute html. Moreover I used a space (" ") as separator in GROUP_CONCAT

CREATE TABLE GC1 (name VARCHAR(20), id INT);

    INSERT INTO GC1 VALUES ('sam',1);
    INSERT INTO GC1 VALUES ('john',2);
    INSERT INTO GC1 VALUES ('joe',3);
    SELECT * FROM GC1;
    SELECT GROUP_CONCAT( CONCAT('<a href="',id,'">',name,'</a>') SEPARATOR " ") AS X FROM GC1;

Output:

<a href="1">sam</a> <a href="2">john</a> <a href="3">joe</a>

GROUP_CONCAT automatically add double quotes only when the field contains double quotes

As mentioned in Marilu's answer, Google mentioned plans to support this use-case but didn't provide an ETA in the feature request at the time of posting. As of February 2015, the issue has been "Fixed".

Google has added a GROUP_CONCAT_UNQUOTED function which behaves nearly identically to GROUP_CONCAT except for it doesn't escape the double quotes.

Here is the description of the function from the Google Docs for BigQuery Aggregate Functions:

GROUP_CONCAT_UNQUOTED('str' [, separator])

Concatenates multiple strings into a single string, where each value is separated by the optional separator parameter. If separator is omitted, BigQuery returns a comma-separated string.

Unlike GROUP_CONCAT, this function will not add double quotes to returned values that include a double quote character. For example, the string a"b would return as a"b.

Example: SELECT GROUP_CONCAT_UNQUOTED(x) FROM (SELECT 'a"b' AS x), (SELECT 'cd' AS x);

How to do group_concat with Integer columns?

Try casting those numeric values to STRING first before rolling them up using GROUP_CONCAT:

SELECT
    date,
    GROUP_CONCAT(endpoint, ', ') AS endpoints,
    GROUP_CONCAT(CAST(success AS STRING), ', ') AS endpoints_successes,  -- cast here
    SUM(success) / SUM(attempts) * 100 AS percentage
FROM some_table
WHERE
    datadate = FROM_TIMESTAMP(DATE_SUB(NOW(), 14), 'yyyyMMdd') AND
    endpoint IN (SELECT endpoint FROM some_table ORDER BY some_priority DESC LIMIT 2)
GROUP BY date
ORDER BY date;

How to use GROUP_CONACT and CONCAT in mysql sub query

If do formally, then

SELECT * 
FROM report 
WHERE FIND_IN_SET( DATE(created_on), 
                   ( SELECT GROUP_CONCAT(DISTINCT DATE(created_on)) 
                     FROM report 
                     WHERE created_on BETWEEN '2020-08-01' AND '2020-08-04' 
                   )
                 ) 
ORDER BY created_on

From the other side - subquery selects all dates in specifies dates range which are present in the table. Outer query selects rows which dates are present in this list, i.e. is present in the table. So ALL table rows within the range will be returned.

---

Related Topics