CODEWITHSUNDEEP
phpmysqlsearchfull-text-searchreplace
Deepu S Nath
Deepu S Nath

Reputation: 1184

MySql search to ignore punctuation, symbols and spaces while data in db has special charactors

I have a huge innoDB Table of inventory over 40 million records which keeps on updated using csv files (files over 1 million data each time). We use Load Data Infile method for importing data to the table.

The table has a filed named part_number which is the unique identifier for each inventory item. The data in this field contains one or more special characters like '-','_','#','$'," " etc..

eg. Abcd-1234

We have a facility for end user to search on this table using part_number as the keyword. There are three types of search a) Exact Match b) Contains With c) Starts With

My requirement is to show users results with special charactors even if they search without those characters like '-','_','#','$'," " etc..

eg. A user should be able to search for Abcd1234 and get the result Abcd-1234. A user should be able to search for Abcd-1234 and get the result Abcd1234 A user should be able to search for Abcd_1234 and get the result Abcd1234 and Abcd-1234

Notes: To avoid table lock during insertion and few other dependencies, I'm compelled to keep table as InnoDB.

Adding a new column that stores part_number omitting punctuations and run the search towards that field was a solution I thought of, but it would affect the data import process which is not acceptable..

I had tried REGEXP but was not successful to suit my criterion. My criteria is as follows a) user can search with or without special characters b) Data in part_number field may or may not contain special character anywhere c) We should be able to show the result in any case..

Please let me know if there is a way to solve this issue..

Upvotes: 1

Views: 3933

Answers (4)

Alexander Stuy
Alexander Stuy

Reputation: 1

Here is another solution:

SELECT REPLACE(REPLACE(REPLACE(REPLACE(cname, '-', ''), '.', ''), '\'', ''), ' ', '') 
AS cna FROM country HAVING cna LIKE   
CONCAT(CONCAT('%',REPLACE(REPLACE(REPLACE(REPLACE('$search_term', '-', ''), '.', ''), '\'', ''), ' ', '')),'%')

Upvotes: 0

Parks
Parks

Reputation: 71

One of the solutions can be to use MySql TABLE VIEW, where you can have column with alphanumeric part_number tag to search on.

Upvotes: 0

bala-16
bala-16

Reputation: 134

Please try this, it will help you
Select REPLACE(cname, '-', '') as cna From country HAVING cna LIKE '%APRE29192%'

Upvotes: 3

Tausen
Tausen

Reputation: 55

Perhaps you could look into REGEXP and make a regular expression that'd suit your needs: http://dev.mysql.com/doc/refman/5.1/en/regexp.html

Upvotes: 0

Related Questions