Search code examples
mysqlsqlregexstringwhere-clause

Check for a character that appears 2 times in mysql

I am trying to check if the o appears 2 times in the string and I did it like this, but it doesn't work, is there a mistake in my code?

SELECT cu.*
FROM Customers cu
WHERE LOWER(cu.FirstName) REGEXP 'o{2}'

-- 'zoom' => correct
-- 'antonio' => correct
-- 'leo jisoo' => fail

thank you

Solution

  • You code checks for two consecutive os, which is not what you want.

    You could write this as:

    where 
    FirstName like '%o%o%'
    and FirstName not like '%o%o%o%'

    This checks if the string contains exactly to os.

    Another approach is:

    length(replace(FirstName, 'o', '')) = length(FirstName) - 2