Lance Strait Lance Strait - 4 months ago 8
SQL Question

Row with most occurrences of a character MySQL

So I have a table with a certain column of type VARCHAR(256). Now I want to find the row(s) of this column with the most commas (since the column contains column seperated values). How would I go about doing this (assuming a MySQL database)?

I was thinking something along the lines of

SELECT LEN(<column_name>)
FROM <table_name>
WHERE Replace(<column_name>, ......
ORDER BY DESC
LIMIT 1;


I don't have a ton of experience with MySQL (or SQL in general) and I could not find much documentation that was helpful, so I would greatly appreciate any help in finishing this off.

vkp vkp
Answer

Subtract the length of column with the length of column after replacing , with '' to get the number of commas. Then order by the calculated column to get the required rows.

SELECT t.*,LEN(column_name) - REPLACE(column_name,',','') num_commas
FROM table_name t
ORDER BY num_commas DESC
LIMIT 1