search without hyphen in mysql

Question

Mysql database contains below kind of values :

'AE01-1056 Ricoh Aficio' OR 'Ricoh AE01-1087 (AE01-1069)' etc

AS if am a normal user i will search the product name in simple text like

AE011056 ... but the result is not found.

i hav tried this query:

$q="SELECT * FROM mytable WHERE (p.product_name LIKE '$name%' OR c.category_name LIKE '$name%' OR pm.name LIKE '$name%')";

What change should i make in my query to get the product , because i have tried LIKE operator & it's not working for me.

You should also use prepared statements for a query that accepts user input. Otherwise you're opening yourself up to SQL injections. — Tim Aagaard, Jan 16 '17 at 07:39
Read here more for complex lookup: http://stackoverflow.com/questions/4671378/levenshtein-mysql-php#4671557 — JustOnUnderMillions, Jan 16 '17 at 07:39
Thank you @tim , but actually the query is in prepared statement.. but just for simplicity i wrote here like this . — neha chaukikar, Jan 16 '17 at 08:47
hi neha... I just gave you some reputation... I'd be happy if you return the favor :p — Anu Shibin Joseph Raj, Jan 18 '17 at 18:15

hkutluay · Answer 1 · 2017-01-16T08:38:15.753

3

Use replace function

$q="SELECT * FROM mytable 
WHERE (REPLACE(p.product_name,'-','') LIKE '%$name%' 
       OR REPLACE(c.category_name,'-','') LIKE '%$name%'
       OR REPLACE(pm.name ,'-','') LIKE '%$name%')";

edited Jan 16 '17 at 08:38

answered Jan 16 '17 at 07:35

hkutluay

6,794
2
33
53

Keep in mind that this will render useless any index on these columns. – shmosel Jan 16 '17 at 07:36
1

thanks @hkutluay, I have tried this also, but not working for me :( – neha chaukikar Jan 16 '17 at 08:37
1

yes @hkutluay, i have checked now only.... right now my query is like this :: (p.product_name LIKE '$name%' OR REPLACE('p.product_name','-','') LIKE '%$name' OR c.category_name LIKE '$name%' OR pm.name LIKE '$name%') – neha chaukikar Jan 16 '17 at 08:44
1

because c.category name and pm.name does not contain hyphen. – neha chaukikar Jan 16 '17 at 08:45
@nehachaukikar ok i see.. you should also use two % after like operator. Like '%$name%'. Because you search text not only begining of the data. – hkutluay Jan 16 '17 at 08:46

score 0 · Answer 2 · answered Jan 16 '17 at 07:42

I think there are only two ways:

1. Manipulate search string If you knwo, users are often search for a code and don't use nessesary hyphens, check the searchstring bevor searching if it follows a given format an insert the hypen if it is missing.

2. replace all hyphens in the where-statement see http://dev.mysql.com/doc/refman/5.7/en/string-functions.html#function_replace

Depending on your setup, solution one might be the more performant solution, since you only have to do one instead multiple stringmanipulations.

search without hyphen in mysql

2 Answers2