CODEWITHSUNDEEP
mysqlsql
Daryll David Dagondon
Daryll David Dagondon

Reputation: 373

MySQL select query to ignore special characters

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.

Upvotes: 1

Views: 1444

Answers (1)

Lokesh Kumar Gaurav
Lokesh Kumar Gaurav

Reputation: 726

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

Upvotes: 4

Related Questions