CODEWITHSUNDEEP
mysql
Bas
Bas

Reputation: 4551

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

Upvotes: 0

Views: 344

Answers (3)

PaulF
PaulF

Reputation: 6773

You could try

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

Upvotes: 1

Scepheo
Scepheo

Reputation: 111

While in certain flavours of SQL INSTR allows specifying a start position, this isn't the case for MySQL. As such, this isn't possible with INSTR without first getting a substring that doesn't include the 'o' in position 2.

That said, LIKE seems a much better fit for what you're trying to achieve:

WHERE naam LIKE '_oo%'

Upvotes: 1

sh88
sh88

Reputation: 106

You have to work with substr

Try this

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

Upvotes: 1

Related Questions