Mysql find records that have the same field value to a specific character

it's not entirely clear what you mean. What should be the result, for example, on such data:

 id value 0 111#12313 1 22#fdsasd 2 22#qweert 3 33333#qwertrewq 4 111#werwr 5 22#qqqq 6 qweqweqw 

Here is a query that counts for each line the number of repetitions of the substring to the # character:

 SELECT * FROM ( SELECT SUBSTR(value, 1, INSTR(value, '#')-1) as pref, COUNT(*) as cnt FROM tablename WHERE INSTR(value, '#') > 0 GROUP BY SUBSTR(value, 1, INSTR(value, '#')-1) /*HAVING COUNT(*) > 1*/ )T1 JOIN tablename T2 ON value LIKE CONCAT(pref, '#%') 

On the above data, the request will issue:

 id value pref cnt 0 111#12313 111 2 1 22#fdsasd 22 3 2 22#qweert 22 3 3 33333#qwertrewq 33333 1 4 111#werwr 111 2 5 22#qqqq 22 3 

If you uncomment /*HAVING COUNT(*) > 1*/ - then the query will output only those lines whose prefix is ​​non-unique (repeated at least once).

UPD: the connection condition is replaced by value LIKE CONCAT(pref, '#%') , so that if there is an index on value - it is used.

If you wish, you can do without two readings from the talename table. MySQL does not have the COUNT(*)OVER(PARTITION BY) function, but this can be arranged using variables in the query. In two stages: first number the lines in each group, then find the maximum number in the group.