Mysql search without special characters

I have a table 'products' which has a column partnumber.

I want to ignore special characters from record while searching.

Suppose i have following 5 records in partnumber:

XP-12345
MV-334-3454
XP1-5555
VX-AP-XP-1000 
VT1232223

Now, If i try to search "XP1", then Output should be come like following records

XP-12345
XP1-5555
VX-AP-XP-1000

How to write mysql query for this ?

deacs

4,2092 gold badges25 silver badges36 bronze badges

asked Apr 22, 2016 at 4:28

You can achieve this functionality using concat[] function. As I can review your and Jorden answer comment that you want to search string XP1 with ignore special charecter like -,_,@ .

So you can use this query

SELECT partnumber FROM products 
   WHERE partnumber LIKE concat['%XP','_','1%'] 
   OR partnumber LIKE '%XP1%';;

Note: Require output you can check on SQLFIDDLE and You can adjust query based on your additional requirement.

answered May 3, 2016 at 9:04

Piyush GuptaPiyush Gupta

2,1513 gold badges13 silver badges28 bronze badges

1

Define a MySQL function which strips the symbols from a provided string.

DELIMITER //
CREATE FUNCTION STRIP_SYMBOLS[input VARCHAR[255]]
RETURNS VARCHAR[255] DETERMINISTIC NO SQL
BEGIN
    DECLARE output VARCHAR[255] DEFAULT '';
    DECLARE c CHAR[1];
    DECLARE i INT DEFAULT 1;
    WHILE i < LENGTH[input] DO
        SET c = SUBSTRING[input, i, 1];
        IF c REGEXP '[a-zA-Z0-9]' THEN
            SET output = CONCAT[output, c];
        END IF;
        SET i = i + 1;
    END WHILE;
    RETURN output;
END//
DELIMITER ;

Then select the records from your table where the partnumber with the symbols stripped contains XP1:

SELECT * FROM products WHERE STRIP_SYMBOLS[partnumber] LIKE '%XP1%';
-- Returns: XP-12345, XP1-5555, VX-AP-XP-1000

This might be painfully slow if your table is large. In this case, look into generated columns [if you have MySQL 5.7.6 or higher] or creating a trigger [if an earlier version] to keep a column in your table updated with the partnumber with symbols stripped.

answered Apr 23, 2016 at 0:12

Matt RainesMatt Raines

4,0698 gold badges30 silver badges33 bronze badges

You need to use REGEXP to allow for containing searches. EX:

SELECT partnumber 
  FROM partnumber_tbl 
 WHERE name REGEXP '[XP1]\-';

This will let it search the database to find anything containing X,P,1.

Here is a live example. And regexp info for you to look more into. Official docs, I hate reading them, especially oracles.

.......

Strawberry

33.5k13 gold badges39 silver badges57 bronze badges

answered Apr 22, 2016 at 4:58

8

You could do something like:

SELECT partnumber
FROM products
WHERE REPLACE[partnumber, "_", ""]] LIKE '%XP1%'];

answered Feb 2, 2021 at 23:44

tschumanntschumann

2,2603 gold badges20 silver badges38 bronze badges

I've been wracking my brain trying to find a solution for this for a couple of days. I'm trying to make a "smart" query that can handle a wide range of search terms. The queries run fine until there are special characters involved and I've had some success w/ the REPLACE method on some characters such as commas and dashes. Other characters such as quotes and ampersands will result in empty queries.

Here's a few examples:

the original name I'm searching for is "French Is Fun, Book 1 - 1 Year Option" and with this query below, I get results returned with these search terms:

1. "French Is Fun"
2. "French Is Fun, book"
3. "French Is Fun, book"
4. "French Is Fun, Book 1"

SELECT * FROM `products` WHERE [ [LOWER[name] LIKE '%french is fun book%' OR
 LOWER[replace[name, '  ','']] LIKE '%french is fun book%' OR
 LOWER[replace[name, ' ','']] LIKE '%french is fun book%' OR
 LOWER[replace[name, '-','']] LIKE '%french is fun book%']

However, when the original title has an ampersand in it like such: "Global History & Geography: The Growth of Civilizations - 1 Year Option" - I get an empty query when I try these different search terms:

1. "Global History & Geography"
2. "Global History Geography"

I've tried this to no avail

SELECT * FROM `products` WHERE  
	[LOWER[name] LIKE '%global history geograph%' OR  
	 	LOWER[replace[name, '  ','']] LIKE '%global history geography%' OR  
	 	LOWER[replace[name, ' ','']] LIKE '%global history geography%' OR 
	 	LOWER[replace[name, ',','']] LIKE '%global history geography%' OR 
  		LOWER[replace[name, '&','']] LIKE '%global history geography%' OR  
	 	LOWER[replace[name, '-','']] LIKE '%global history geography%'];

I also tried adding an escape character to the ampersand and it doesn't help:

SELECT * FROM `products` WHERE  
	[LOWER[name] LIKE '%global history geography%' OR  
	 	LOWER[replace[name, '  ','']] LIKE '%global history geography%' OR  
	 	LOWER[replace[name, ' ','']] LIKE '%global history geography%' OR 
	 	LOWER[replace[name, ',','']] LIKE '%global history geography%' OR 
  		LOWER[replace[name, '\&','']] LIKE '%global history geography%' OR  
	 	LOWER[replace[name, '-','']] LIKE '%global history geography%'];

And commas in the name also return empty results. As a demonstration, the original name is this:

"Amsco's AP Calculus AB/BC Preparing for the Advanced Placement Examinations - 1 Year Option"

This attempt always returns empty queries:

SELECT * FROM `products` WHERE 
	[ [LOWER[name] LIKE '%amscos ap calculus%' OR
		 LOWER[replace[name, ' ','']] LIKE '%amscos ap calculus%' OR
		 LOWER[replace[name, '\'','']] LIKE '%amscos ap calculus%' OR
		 LOWER[replace[name, ',','']] LIKE '%amscos ap calculus%' OR
		 LOWER[replace[name, '-','']] LIKE '%amscos ap calculus%']
		] AND [ [`products`.`type` = 'Rental' ] ];

Any ideas?

How do I remove special characters from a MySQL query?

What does MySQL_ real_ escape_ string do?

How do I find special characters in MySQL?

How do you handle special characters in SQL?