Solved

How to generate list of unique values in csv column?

Posted on 2014-01-18
2
544 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
2 Comments
 
LVL 34

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

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

All XML, All the Time; More Fun MySQL Tidbits – Dynamically Generate XML via Stored Procedure in MySQL Extensible Markup Language (XML) and database systems, a marriage we are seeing more and more of.  So the topics of parsing and manipulating XM…
Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL (http://www.experts-exchange.com/articles/201/Handling-Date-and-Time-in-PHP-and-MySQL.html) several years ago, it seemed like now was a good time to updat…
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

760 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now