Solved

How to generate list of unique values in csv column?

Posted on 2014-01-18
2
558 Views
Last Modified: 2014-01-19
I have to work with a non-normalized table in MYSQL which I do not have the ability to alter.

The table has a column (col1) which has comma separated values.

I need to generate a list of distinct values within all rows for col1.

Can someone please provide me a query to do that?

col1
------
a,b,c
b,a,g
g,h

Desired result:
a
b
c
g
h
0
Comment
Question by:SAbboushi
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 35

Accepted Solution

by:
Dan Craciun earned 500 total points
ID: 39791863
First of all, MySQL is not really the tool for this. You can use the language of your choice and simply do a split, explode, whatever.

However, if you insist, it can be done :)

First, you would need to define a split function. Here's the code, copied from here: http://injustfiveminutes.com/2012/10/17/split-function-in-mysql/
CREATE FUNCTION SPLIT_STR(
  x VARCHAR(255),
  delim VARCHAR(12),
  pos INT
)
RETURNS VARCHAR(255)
RETURN REPLACE(SUBSTRING(SUBSTRING_INDEX(x, delim, pos),
       LENGTH(SUBSTRING_INDEX(x, delim, pos -1)) + 1),
       delim, '');

Open in new window


Then, you could use this in your SQL:
CREATE TEMPORARY TABLE temp (val CHAR(255));
INSERT INTO temp(val) VALUES (SELECT SPLIT_STR(col1, ",", 1) FROM your-table), (SELECT SPLIT_STR(col1, ",", 2) FROM your-table), (SELECT SPLIT_STR(col1, ",", 3) FROM your-table);
SELECT DISTINCT val FROM temp;

Open in new window


The code is not tested, but you get the idea. You would need more SPLIT_STR calls if you have more than 3 joined values.

HTH,
Dan
0
 

Author Closing Comment

by:SAbboushi
ID: 39793221
K thanks--
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction In this installment of my SQL tidbits, I will be looking at parsing Extensible Markup Language (XML) directly passed as string parameters to MySQL 5.1.5 or higher. These would be instances where LOAD_FILE (http://dev.mysql.com/doc/refm…
More Fun with XML and MySQL – Parsing Delimited String with a Single SQL Statement Are you ready for another of my SQL tidbits?  Hopefully so, as in this adventure, I will be covering a topic that comes up a lot which is parsing a comma (or other…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…

733 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question