How to avoid Space sensitivity of MySQL data

Question

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'}

Answer 1

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                |
+------------------------+-----------------------+

See also:

• https://dev.mysql.com/doc/refman/8.0/en/string-functions.html#function_soundex
• https://en.wikipedia.org/wiki/Soundex