user961743 user961743 - 10 days ago 4
SQL Question

Maximum Count Repeating Characters in Oracle SQL Column

I have a column in a database of license numbers. I am trying to determine if the numbers are legitimate. One of the tests is to determine if there are multiple occurrences of the same character. I'm trying to determine what the count of the most repeated character is. The second test is to determine if all the numbers are sequential, but I'm posting that as a different question.

An example of what I'm looking for:

LICENSE_NUMBER MAX_COUNT
111246544 3
999999999 9
123456789 0
AAAAAAAAA 9
A12345667 2


Thanks for your help!

Answer

That's not the prettiest code and if licence can contain more characters (I gues it is hexadecimal) it is going to be long query but you can try:

select licence_number, greatest(REGEXP_COUNT(licence_number, '1'), 
  REGEXP_COUNT(licence_number, '2'), REGEXP_COUNT(licence_number, '3'), 
  REGEXP_COUNT(licence_number, '4'), REGEXP_COUNT(licence_number, '5'), 
  REGEXP_COUNT(licence_number, '6'), REGEXP_COUNT(licence_number, '7'), 
  REGEXP_COUNT(licence_number, '8'), REGEXP_COUNT(licence_number, '9'), 
  REGEXP_COUNT(licence_number, '0'), REGEXP_COUNT(licence_number, 'A'),
  REGEXP_COUNT(licence_number, 'B'), REGEXP_COUNT(licence_number, 'C'),
  REGEXP_COUNT(licence_number, 'D'), REGEXP_COUNT(licence_number, 'E'),
  REGEXP_COUNT(licence_number, 'F') ) as max_count
from table;
Comments