select 
device_id,
(char_length(nick_name) - char_length(substring_index(nick_name,'abc',-1)) - char_length('abc') + 1) as last_idx,
concat('abc',substring_index(nick_name,'abc',-1)) as check_str
from user_submit where nick_name like '%abc%'
order by device_id desc