CODEWITHSUNDEEP
mysqlsql
Lance Strait
Lance Strait

Reputation: 4261

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.

Upvotes: 0

Views: 104

Answers (2)

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49270

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) - LEN(REPLACE(column_name,',','')) num_commas
FROM table_name t
ORDER BY num_commas DESC
LIMIT 1

Edit: As pointed out by @Strawberry in the comment, the query above would pick up an arbitrary row from the table in case of a tie. To get all the rows when there is a tie, use

SELECT t.* 
FROM tablename t
JOIN (SELECT MAX( LEN(column_name) - LEN(REPLACE(column_name,',','')) ) max_commas
      FROM table_name) m 
ON m.max_commas =  LEN(column_name) - LEN(REPLACE(column_name,',',''))

Upvotes: 4

Faizan Younus
Faizan Younus

Reputation: 803

Try this.

select max(length(password)-length(replace(password,'$',''))) from [YOUR TABLE];

Hope this helps.

Upvotes: 0

Related Questions