MySQL search - escape special characters from column

Question

Hi I want to search string by escaping special characters using Like query.

My String is - abc & abc

Column value is - abc & abc

Somehow I don't want to include special characters in this search functionality.

I can escape my string using REGEX by using preg_replace with /[^A-Za-z0-9\-]/

What I want is some mysql function which can replace the column value for getting search result only but it should not update the column value.

So I will pass abc abc string and it will match column with value abc abc.

Virtually - SELECT * FROM MyTable WHERE myColumn LIKE 'abc abc';

and it should give me the result.

basically you want to ignore all special characters in where clause? — Anil, Dec 09 '14 at 10:48
You need to review your question. First you talk about "escape" and then you suggest `preg_replace` (escape != replace). Again you talk about "search" and then "should not update". — PauloASilva, Dec 09 '14 at 10:49
I will use preg replace to ignore all characters from my string using php and then it will match using like clause — D3Systems, Dec 09 '14 at 10:51
Go through this http://stackoverflow.com/questions/6942973/mysql-how-to-remove-all-non-alpha-numeric-characters-from-a-string. You need to write a custom function to replace all special chars to blank/space — Anil, Dec 09 '14 at 10:54

Anil · Answer 1 · 2014-12-09T11:32:05.413

DROP FUNCTION IF EXISTS RemoveSpecialChar; 

DELIMITER $ 

CREATE FUNCTION RemoveSpecialChar(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) NOT IN(35, 38,37, 42, 64, 94) THEN 
                SET out_str = CONCAT(out_str, c); 
          END IF; 

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

  RETURN out_str; 

END$ 

DELIMITER ;

below query is working

SELECT RemoveSpecialChar(loginPass) FROM users

You need to add ascii value of all required character in below if of function

 IF ASCII(c) NOT IN(35, 38,37, 42, 64, 94) THEN

So your query will be

SELECT * FROM MyTable WHERE myColumn LIKE RemoveSpecialChar('abc & abc')

I executed the above successfully. Now it is giving me error - FUNCTION testdb.RemoveSpecialChar does not exist — D3Systems, Dec 09 '14 at 11:07
updated my answer, check now. I have also changed function definition. — Anil, Dec 09 '14 at 11:34

MySQL search - escape special characters from column

1 Answers1