How to avoid Space sensitivity of MySQL data-CodePudding

I have a record like "John Carter" with the space between two names. Now I want to search the word with the help of WHERE condition like (WHERE NAME="JohnCarter") without space, but due to the space sensitivity query returns me false, so is there any solution to avoid this space sensitivity.

Id	name
1	John Carter
2	Rosy Foster

$name='JohnCarter'; $result=mysqli_query($con,"SELECT * FROM profile WHERE name='$name'"); if(mysqli_num_rows($result)>0){ echo 'true';}else{echo 'false'}

CodePudding user response：

This seems to be the ideal situation to use a soundex function. MySQL has such a function builtin:

mysql> select soundex('John Carter'), soundex('JhonCarter');
 ------------------------ ----------------------- 
| soundex('John Carter') | soundex('JhonCarter') |
 ------------------------ ----------------------- 
| J52636                 | J52636                |
 ------------------------ -----------------------