Selecting rows where first n-chars are equal (MySQL)

Question

I have a table with playerhandles, like this:

1 - [N] Laka
2 - [N] James
3 - nor | Brian
4 - nor | John
5 - Player 2
6 - Spectator
7 - [N] Joe

From there I wanna select all players where the first n-chars match, but I don't know the pattern, only that it's the first n-chars. In the above example I wan't it to return rows 1,2,3,4 and 7.

Is this possible in MySQL? And not too expensive...

Thanks! =)

Answer 1

If you know the value of n, you could do something like this (for n=3):

Select *
FROM players
WHERE Left(name, 3) in (
   SELECT Left(name, 3)
   FROM players
   GROUP BY Left(name, 3)
   HAVING (Count(*) > 1)
);