Bas Bas - 3 months ago 6
MySQL Question

Search in string for 2 of the same characters after each other

I need to search for a name where the second and third character are oo. And the lastname isn't Tomeloos.

I came up with this query

SELECT * FROM student
WHERE INSTR(naam, 'o') = 2 AND INSTR(naam, 'o') = 3
AND naam NOT LIKE '%Tomeloos';


But like expected this doesnt work because this part will never return true:

AND INSTR(naam, 'o') = 3


Because it will see an o at the second position.

Does anyone know how I can achieve this with the
INSTR
function?
Thanks

Answer

You could try

SELECT * FROM student
WHERE SUBSTR(naam, 2, 2) = 'oo'
AND naam NOT LIKE '%Tomeloos';