Deepu S Nath Deepu S Nath - 4 months ago 14
SQL Question

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..

Answer

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