To count the number of characters in a string (simply the character count), use the function:
CHAR_LENGTH(str)
for example:
SELECT CHAR_LENGTH(mycol) FROM mytable;
To count the number of certain characters (for example, delimiters) in a string, you'll need to write a query (for example, to count the number of ':' characters):
SELECT (CHAR_LENGTH(mycolumn) - CHAR_LENGTH(REPLACE(mycolumn,':',''))) div CHAR_LENGTH(':') FROM mytable;
That is, we:
a) Count the total number of characters
b) Replace the characters we need with nothing
c) Count the number of characters in the string without the ones we need to count
d) Since the "delimiter" could be a multibyte string - we count the length of the string
e) By dividing, we get the number of occurrences of the delimiter in the string
To calculate the maximum number of characters across all rows of a given column - add the MAX() function in front of the query above:
SELECT MAX((CHAR_LENGTH(mycolumn) - CHAR_LENGTH(REPLACE(mycolumn,':',''))) div CHAR_LENGTH(':')) FROM mytable;
Comments