Mysql search with ignore special characters -

i have table 'products' has column partnumber.

i want ignore special characters record while searching.

suppose have following 5 records in partnumber:

xp-12345 mv-334-3454 xp1-5555 vx-ap-xp-1000  vt1232223 

now, if try search "xp1", output should come following records

xp-12345 xp1-5555 vx-ap-xp-1000 

how write mysql query ?

define mysql function strips symbols provided string.

delimiter // create function strip_symbols(input varchar(255)) returns varchar(255) deterministic no sql begin     declare output varchar(255) default '';     declare c char(1);     declare int default 1;     while < length(input)         set c = substring(input, i, 1);         if c regexp '[a-za-z0-9]'             set output = concat(output, c);         end if;         set = + 1;     end while;     return output; end// delimiter ; 

then select records table partnumber symbols stripped contains xp1:

select * products strip_symbols(partnumber) '%xp1%'; -- returns: xp-12345, xp1-5555, vx-ap-xp-1000 

this might painfully slow if table large. in case, generated columns (if have mysql 5.7.6 or higher) or creating trigger (if earlier version) keep column in table updated partnumber symbols stripped.