MySQL select statement remove whitespace in WHERE clause -

i'm trying match phone numbers based on last 6 digits. problem numbers in database in various formats, have whitespace within number.

select * users trim(phone) '%123456'  

trim removes leading , trailing whitespace , doesn't find entries clients have entered numbers whitespace between numbers: 123 456, 12 34 56, etc.

so how remove whitespace within search? having result without whitespace not enough. updating database replace not option either.

use replace() substitute occurrences of ' ' '' within string.

mysql documentation

replace(str,from_str,to_str) 

returns string str occurrences of string from_str replaced string to_str. replace() performs case-sensitive match when searching from_str.

select * users replace(coalesce(phone,''), ' ','') '%123456' 

if can't in clause, seems odd me; nest in select.

select sub.*  (select u.*, replace(coalesce(phone,''), ' ','') ph                users u) sub sub.ph '%123456'