CODEWITHSUNDEEP
phpmysql
NaughtySquid
NaughtySquid

Reputation: 2097

MySQL search without any special characters

I am wondering if it's possible to search through a MySQL field where the field may have something like this:

test - hello

but you have in a string from a user

test: hello

Obviously it's easy on PHP-side to strip the string of any special characters like that, but is it possible to search through MySQL rows and ignore special characters?

Upvotes: 2

Views: 3460

Answers (3)

Tim Dearborn
Tim Dearborn

Reputation: 1177

Another unique solution is to put wildcards in between each word. As long as the search phrase does not have special characters in it, the correct results will be returned while ignoring any special characters in the results.

For example...

SELECT *
FROM myTable
WHERE somefield LIKE '%test%hello%'

Upvotes: 4

Bill Karwin
Bill Karwin

Reputation: 562951

You can sort of "ignore" special characters, whitespace, etc. by using the SOUNDEX() function:

mysql> select soundex('test - hello'), soundex('test: hello');
+-------------------------+------------------------+
| soundex('test - hello') | soundex('test: hello') |
+-------------------------+------------------------+
| T234                    | T234                   |
+-------------------------+------------------------+

So you can search your data like this:

SELECT ...
FROM MyTable
WHERE SOUNDEX(somefield) = SOUNDEX('test: hello');

This won't be indexable at all, so it'll be forced to do a table-scan. If you use MySQL 5.7, you could add a virtual column for the soundex expression, and index that virtual column. That would help performance a lot.

Upvotes: 2

Faizan Younus
Faizan Younus

Reputation: 803

It could be possible if you find and replace all such special character and spaces from user input also column, i.e.

select * from tablename where replace(replace(columnname,' ',''),':',''),'-','')=replace(replace([USER INPUT],' ',''),':',''),'-','');

Upvotes: 1

Related Questions