24 October 2010

MySQL functions to count occurences of specified char or string

Because doing calculations directly on the database is often faster

Let's say our column in named 'col' and table is named 't2'.

Count occurrences of specified character within a string

SELECT
	LENGTH(
		REPLACE(col, '.', '@@')
		) -
	LENGTH(
		col
		)		
FROM t2

Will count number of occurrences of character '.' in column 'col'

SELECT
ROUND((
	LENGTH(
		REPLACE(col, 'ab', REPEAT('ab', 2))
		) -
	LENGTH(
		col
		) 
) / 2)		
FROM t2

Will count number of occurrences of substring 'ab' in column 'col'