Jelly and Custard

Count occurence of character (i.e. line breaks) in a MySQL field

November 8th, 2005

Unfortunately, there is no inbuilt MySQL function to count the occurance of a character in string. By counting the number of characters in the original string, then temporarily 'deleting' the character you want to count and counting the string again, subtracting the first count from the second, you get the number of occurances:

SELECT LENGTH('foobarfoobarfoobar') - LENGTH(REPLACE('foobarfoobarfoobar', 'b', '')) AS `occurances`
--> 3

Obviously, in this example 'b' is the string you want to count the number of occurances of.

You can use the same principle to count the number of lines in a field, by adding one to the difference:

SELECT LENGTH(`foo`) - LENGTH(REPLACE(`foo`, 'n', '')) + 1 AS `numLines` FROM `bar`

Maybe that'll save you some hunting around!

 Add to del.icio.us    Digg this    Technorati

Related Posts:

• Retrieve valid entries for a MySQL ENUM/SET column
• Dates & MySQL
• Regular Expressions in PHP
• Getting totals in MySQL
• MySQL Events

Entry Filed under: MySQL