I have the following phone numbers in a column:
["+63(02)3647766", "+63(02)5467329", "+63(02)8555522", "+63(02)3642403"]
How can I get that info like this:
+63(02)3647766,+63(02)5467329,+63(02)8555522,+63(02)3642403
I have the following phone numbers in a column:
["+63(02)3647766", "+63(02)5467329", "+63(02)8555522", "+63(02)3642403"]
How can I get that info like this:
+63(02)3647766,+63(02)5467329,+63(02)8555522,+63(02)3642403
i think this is the most only MySQL clean way, atleast for MySQL versions under 8
Query
SET SESSION group_concat_max_len = @@max_allowed_packet;
SELECT
GROUP_CONCAT(
JSON_UNQUOTE(
JSON_EXTRACT(records.json, CONCAT('$[', number_generator.number , ']'))
)
)
FROM (
SELECT
@row := @row + 1 AS number
FROM (
SELECT 0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9
) row1
CROSS JOIN (
SELECT 0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9
) row2
CROSS JOIN (
SELECT @row := -1
) init_user_params
) AS number_generator
CROSS JOIN (
SELECT
json
, JSON_LENGTH(records.json) AS json_array_length
FROM (
SELECT
'["+63(02)3647766", "+63(02)5467329", "+63(02)8555522", "+63(02)3642403"]' AS json
FROM
DUAL
) AS records
) AS records
WHERE
number BETWEEN 0 AND json_array_length - 1
Result
| GROUP_CONCAT(
JSON_UNQUOTE(
JSON_EXTRACT(records.json, CONCAT('$[', number_generator.number , ']'))
)
) |
| -------------------------------------------------------------------------------------------------------------------------- |
| +63(02)3647766,+63(02)5467329,+63(02)8555522,+63(02)3642403 |
see demo
Have you heard of JSON_TABLE()? – oysteing
I have, i dont assume everybody to be on MySQL 8 already but i added it for completeness also.
MySQL 8.0 query only
SET SESSION group_concat_max_len = @@max_allowed_packet;
SELECT
GROUP_CONCAT(item)
FROM JSON_TABLE(
'["+63(02)3647766", "+63(02)5467329", "+63(02)8555522", "+63(02)3642403"]'
, "$[*]"
COLUMNS (
rowid FOR ORDINALITY
, item VARCHAR(100) PATH "$"
)
) AS json_parsed
Result
| GROUP_CONCAT(item) |
| ----------------------------------------------------------- |
| +63(02)3647766,+63(02)5467329,+63(02)8555522,+63(02)3642403 |
see demo
The REPLACE()
nesting method is more messy, but should work on all MySQL versions.
SELECT
REPLACE(
REPLACE(
REPLACE(
'["+63(02)3647766", "+63(02)5467329", "+63(02)8555522", "+63(02)3642403"]'
, '['
, ''
)
, ']'
, ''
)
, '"'
, ''
)
Result
| REPLACE(
REPLACE(
REPLACE(
'["+63(02)3647766", "+63(02)5467329", "+63(02)8555522", "+63(02)3642403"]'
, '['
, ''
)
, ']'
, ''
)
, '"'
, ''
) |
| --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- |
| +63(02)3647766, +63(02)5467329, +63(02)8555522, +63(02)3642403 |
see demo
so if you have replace replace replace function. Its super cool and easy. if you want to build varchar values comma separated then use the following;
@json_array = ["value1", "value2"]
select replace(replace(replace(json_extract(@json_array, '$'), '"', '\''), '[', ''), ']', '');
But if you want to build numeric then use
select replace(replace(replace(json_extract(@json_array, '$'), '"', ''), '[', ''), ']', '');enter code here
Raymond already answered the question, but I am posting my answer to just make it clear when you get the JSON value directly from a table column. Let's say you have a person table and person table has phoneNoJson column in json format
select
p.phoneNosJson,
(
select
group_concat(phoneNo.value)
from
json_table(p.phoneNosJson, '$[*]' columns (value text path '$')) phoneNo
) phoneNosCommaSeparated
from
person p
/*
Result
if
p.phoneNosJson == ["+63(02)3647766", "+63(02)5467329", "+63(02)8555522", "+63(02)3642403"]
phoneNosCommaSeparated will be
+63(02)3647766,+63(02)5467329,+63(02)8555522,+63(02)3642403
*/
Hope my answer help to anybody having the same problem!!!
The most suitable solution for all MySQL versions is:
SELECT
REPLACE(
REPLACE(
REPLACE(
records.json,
'[',
''
),
']',
''
),
'"',
''
) AS comma_separated
FROM (
SELECT
'["+63(02)3647766", "+63(02)5467329", "+63(02)8555522", "+63(02)3642403"]' AS json
) AS records;
Looks heavy, but it works like a charm.
if it is not the query you want to do here is a simple function you can pass your array and get back the the string.
$data = ["+63(02)3647766", "+63(02)5467329", "+63(02)8555522", "+63(02)3642403"];
function MakeAstring($data){
$array_with_comma = array();
$last_key = end(array_keys($data));
foreach($data as $key => $number)
{
$string .= $number.($key != $last_key ? ',': '');
}
return $string;
}
echo MakeAstring($data);