MySQL select query to ignore special characters

Question

Take for example in a column "company":

+---------------+
|company        |
+---------------+
|MyCompany, Inc.|
+---------------+

Supposing the user just types "MyCompany Inc" into the search query without coma and period.

How do I make an MySQL Select query that will still returns "MyCompany, Inc." ?

Not just for coma and period but all special characters must be ignored.

I mean you want to search MyCompany from company column and you need to ignore other characters. Am I right? — Lokesh Kumar Gaurav, Nov 06 '17 at 05:58
Yes. Ignore special characters because generally users don't know if companies have special characters in their names. — Daryll David Dagondon, Nov 06 '17 at 06:00
Miggy. How do you LIKE query in this scenario? where company like '%MyCompany Inc%'? or where company like 'MyCompany Inc'? They are returning empty. — Daryll David Dagondon, Nov 06 '17 at 06:28
Possible duplicate of [mySQL set a varchar without the special characters](https://stackoverflow.com/questions/24013238/mysql-set-a-varchar-without-the-special-characters) — Balasubramanian, Nov 06 '17 at 07:15
@Balasubramanian I don't think it's a duplicate of that question.. that is asking how to strip data out of a string before storing the string to table. This question seems to be asking "how can I return a result of "MyCompany, Inc." if the user types "mycompany inc" into my program's search box? He is looking for fuzzy match of "mycompany inc" similar to "MyCompany, Inc." — Caius Jard, Nov 06 '17 at 10:28
You seem to be asking how to implement a search that looks for text that is similar to what the user typed, rather than exactly what the user typed. I'm voting to close this one, but I'm linking a different duplicate question than Balasubramanian suggested — Caius Jard, Nov 06 '17 at 10:29
Possible duplicate of [How to find similar results and sort by similarity?](https://stackoverflow.com/questions/3338889/how-to-find-similar-results-and-sort-by-similarity) — Caius Jard, Nov 06 '17 at 10:30
@DaryllDavidDagondon ps; when Miggy suggested what he did he probably meant to use LIKE the other way around: `SELECT * FROM companies where 'mycompany inc' LIKE REPLACE(REPLACE(company, ',', '%'), '.', '%')` - this query replaces comma and period in the column with %, and then compares it to what the user typed using LIKE. It's naive, but it'd work. Better solutions exist, i'm sure — Caius Jard, Nov 06 '17 at 10:35
@Caius Jard. Best solution so far. Works best on fewest characters. But if I want to exclude all special characters in a query, I got to enumerate more REPLACE function in an SQL statement. — Daryll David Dagondon, Nov 07 '17 at 01:11
That's the thing with quick and dirty hacks - they're quick, and dirty. Perhaps you should look at Lokesh's solution, and have another column in the table that stores the company name with just ascii characters. When the user types the search into the front end app, use a regular expression to strip all non ascii chars, replace spaces with % and then do a `my_cleaned_comapny_name_column LIKE @cleaned_user_search_string` if you want to keep the sql simple looking — Caius Jard, Nov 07 '17 at 10:15

score 4 · Answer 1 · answered Nov 06 '17 at 06:37

You have to create mysql function-

 CREATE FUNCTION `removeSpacialChar`(`in_str` varchar(4096)) RETURNS varchar(4096) CHARSET utf8 
BEGIN
      DECLARE out_str VARCHAR(4096) DEFAULT ''; 
      DECLARE c VARCHAR(4096) DEFAULT ''; 
      DECLARE pointer INT DEFAULT 1; 

      IF ISNULL(in_str) THEN
            RETURN NULL; 
      ELSE
            WHILE pointer <= LENGTH(in_str) DO 

                  SET c = MID(in_str, pointer, 1); 

                  IF (ASCII(c) >= 48 AND ASCII(c) <= 57) OR (ASCII(c) >= 65 AND ASCII(c) <= 90) OR (ASCII(c) >= 97 AND ASCII(c) <= 122) THEN
                      SET out_str = CONCAT(out_str, c); 
                  ELSE
                      SET out_str = CONCAT(out_str, '');   
                  END IF; 

                  SET pointer = pointer + 1; 
            END WHILE; 
      END IF; 

      RETURN out_str; 
END

Now you run below query

SELECT removeSpacialChar('abc&*&*%^ %*^*%^,--&*&^^%^%^^&^%%^%$$#%@#$@$@!@$!123');

Output:-

abc123

A few million rows in the table and the db engine goes nuts. — Fr0zenFyr, May 03 '19 at 10:56

MySQL select query to ignore special characters

1 Answers1