Choosing strings where the first n-characters are equal (MySQL)

Question

I have a table with game controllers, for example:

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

From there I want to select all the players where the first n-characters match, but I don’t know the pattern, only these are the first n-characters. In the above example, I will not return rows 1,2,3,4 and 7.

Is this possible and not too expensive in MySQL?

Answer 1

You can add an exists clause.

 select name from players p1 where exists ( select 1 from players p2 where p2.name like CONCAT( SUBSTRING(p1.name, 1, 3), '%') and p1.name <> p2.name ) 

This will give you:
1 - [N] Laka
2 - [N] James
3 - not | Brian
4 - not | John
7 - [N] Joe

Add a “name” by name, and you can do the rest of your work in code.

Answer 2

If you know the value of n, you can 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) );