You came to the right place !!! Hang onto something because you are going to roll on the floor laughing
I answered a similar question just like this back on April 22, 2014
involving apples
, oranges
, pears,
and figs
: MySQL group concat not showing distinct values when joined to another table
In your case, you need to assemble distinct fruits by the id it belongs to
Ready for a convoluted solution tailor made for you ?
PROPOSED QUERY
SELECT CONCAT('SELECT id,REPLACE(GROUP_CONCAT(DISTINCT frt),'','','', '') tag FROM (',
CONCAT('SELECT ',GROUP_CONCAT(fruitstr SEPARATOR ' UNION SELECT ')),') A GROUP BY id')
INTO @TagSQL FROM
(
SELECT REPLACE(CONCAT(REPLACE(QUOTE(tag),', ',CONCAT(''',',
id,' id UNION SELECT ''')),',',id,' id'),',',' frt,') fruitstr FROM fruit
) A;
SELECT @TagSQL\G
PREPARE s FROM @TagSQL; EXECUTE s; DEALLOCATE PREPARE s;
YOUR SAMPLE DATA
mysql> drop database if exists dinesh;
Query OK, 2 rows affected (0.38 sec)
mysql> create database dinesh;
Query OK, 1 row affected (0.01 sec)
mysql> use dinesh
Database changed
mysql> create table fruit
-> (
-> id int not null auto_increment primary key,
-> tag varchar(100) not null
-> );
Query OK, 0 rows affected (0.28 sec)
mysql> insert into fruit (tag) values
-> ('apples, oranges, apples'),
-> ('apples, pears, pears'),
-> ('figs, oranges, figs, oranges');
Query OK, 3 rows affected (0.03 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from fruit;
+----+------------------------------+
| id | tag |
+----+------------------------------+
| 1 | apples, oranges, apples |
| 2 | apples, pears, pears |
| 3 | figs, oranges, figs, oranges |
+----+------------------------------+
3 rows in set (0.00 sec)
mysql>
PROPOSED QUERY EXECUTED
mysql> SELECT CONCAT('SELECT id,REPLACE(GROUP_CONCAT(DISTINCT frt),'','','', '') tag FROM (',
-> CONCAT('SELECT ',GROUP_CONCAT(fruitstr SEPARATOR ' UNION SELECT ')),') A GROUP BY id')
-> INTO @TagSQL FROM
-> (
-> SELECT REPLACE(CONCAT(REPLACE(QUOTE(tag),', ',CONCAT(''',',
-> id,' id UNION SELECT ''')),',',id,' id'),',',' frt,') fruitstr FROM fruit
-> ) A;
Query OK, 1 row affected (0.03 sec)
mysql> SELECT @TagSQL\G
*************************** 1. row ***************************
@TagSQL: SELECT id,REPLACE(GROUP_CONCAT(DISTINCT frt),',',', ') tag FROM (SELECT 'apples' frt,1 id UNION SELECT 'oranges' frt,1 id UNION SELECT 'apples' frt,1 id UNION SELECT 'apples' frt,2 id UNION SELECT 'pears' frt,2 id UNION SELECT 'pears' frt,2 id UNION SELECT 'figs' frt,3 id UNION SELECT 'oranges' frt,3 id UNION SELECT 'figs' frt,3 id UNION SELECT 'oranges' frt,3 id) A GROUP BY id
1 row in set (0.00 sec)
mysql> PREPARE s FROM @TagSQL; EXECUTE s; DEALLOCATE PREPARE s;
Query OK, 0 rows affected (0.00 sec)
Statement prepared
+----+-----------------+
| id | tag |
+----+-----------------+
| 1 | apples, oranges |
| 2 | apples, pears |
| 3 | figs, oranges |
+----+-----------------+
3 rows in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
mysql>
GIVE IT A TRY !!!
P.S. Like I said in my earlier post,
I TOLD YOU IT WAS CONVOLUTED !!!
The thing is that here you have to combine two literals: '{}'
for the array and '[...,...]'
for the range type.
In all these cases, the easiest is just getting back the output from the system itself:
SELECT array[tstzrange('2014-01-01','2015-01-01','[]')];
array
─────────────────────────────────────────────────────────────
{"[\"2014-01-01 00:00:00+01\",\"2015-01-01 00:00:00+01\"]"}
Now you simply wrap this into quotes, and there you are.
There is, however, a simpler way, which usually works: just double-quote the inner literal. See:
select '{"[2014-01-01,2015-01-01]"}'::tstzrange[];
tstzrange
─────────────────────────────────────────────────────────────
{"[\"2014-01-01 00:00:00+01\",\"2015-01-01 00:00:00+01\"]"}
Note that the output is the same as above.
Best Answer
Here is what I ended up using based on @a_horse_with_no_name's comment.
The idea is that the
format
function (with%I
) will not add any quotes if it is unnecessary (i.e. when the input is already a simple identifier). I also normalized identifier names to lower cases usinglower()
before the comparison, which may or may not be desirable depending on whether the identifiers are supposed to be case insensitive or sensitive.Sample output: