Solved

How to generate list of unique values in csv column?

Posted on 2014-01-18
2
550 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
PHP/MySQL Time slot availability check for staff id and all staff 5 96
MySQL: Get All Duplicates (not grouped) 3 50
Mysql Crashing Intermittently 16 82
join tables 4 50
This guide whil teach how to setup live replication (database mirroring) on 2 servers for backup or other purposes. In our example situation we have this network schema (see atachment). We need to replicate EVERY executed SQL query on server 1 to…
Both Easy and Powerful How easy is PHP? http://lmgtfy.com?q=how+easy+is+php (http://lmgtfy.com?q=how+easy+is+php)  Very easy.  It has been described as "a programming language even my grandmother can use." How powerful is PHP?  http://en.wikiped…
In this video I am going to show you how to back up and restore Office 365 mailboxes using CodeTwo Backup for Office 365. Learn more about the tool used in this video here: http://www.codetwo.com/backup-for-office-365/ (http://www.codetwo.com/ba…
Video by: Mark
This lesson goes over how to construct ordered and unordered lists and how to create hyperlinks.

910 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

21 Experts available now in Live!

Get 1:1 Help Now