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?