CODEWITHSUNDEEP
mysqlsqlregexstringwhere-clause
Claire Duong
Claire Duong

Reputation: 57

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

Upvotes: 1

Views: 1693

Answers (2)

GMB
GMB

Reputation: 222502

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

Upvotes: 5

Manash Kumar
Manash Kumar

Reputation: 1085

Try:

SELECT cu* FROM Customers cu WHERE LENGTH(LOWER(cu.FirstName)) - LENGTH(REPLACE(LOWER(cu.FirstName), 'o', '')) = 2;

Upvotes: 1

Related Questions