CODEWITHSUNDEEP
mysql
jonderry
jonderry

Reputation: 23633

Get distinct values from a comma-delimited column in a MySQL table?

I have a column in a MySQL table that consists of comma-delimited strings. I would like to convert this column into a set of distinct strings that occur in the column (for any row in the table -- the set includes strings that occur in any row of the table in this column). What is the easiest way to accomplish this?

The solution doesn't need to be pure MySQL. It could involve unix, perl, etc.

Upvotes: 1

Views: 2190

Answers (2)

jonderry
jonderry

Reputation: 23633

The way I chose was to run the select mysql command outside of the mysql shell and pipe the results into tr and sort --uniq

mysql my_db [-p -u -h] -se "select my_column from my_table;" | tr ',' '\n' | sort -u

This is pretty simple and seems to give the correct results as far as I can tell.

Upvotes: 1

Ike Walker
Ike Walker

Reputation: 65547

You could probably get a quick-and-dirty list of distinct strings from a comma-delimited column using SELECT INTO OUTFILE, sed, and LOAD DATA INFILE.

Basically you want to dump the text data to a flat file, using a comma as the line delimiter so each string will be treated as a separate row when you load it back into the database. Then load the extracted into a new table and select the distinct values.

Off the top of my head, the code would look something like this:

select str 
into outfile
'/tmp/your_table_data.txt'
lines terminated by ','
from your_table;

sed -e 's/\\,/,/g' -e 's/,$//' /tmp/your_table_data.txt >  /tmp/commadelimited.txt

create table your_table_parsed(str text);

load data infile '/tmp/commadelimited.txt' 
ignore into table your_table_parsed 
fields terminated by ',' 
lines terminated by ',';

select distinct str from your_table_parsed;

Upvotes: 1

Related Questions