1

I am working on searching part, I am facing one issue for searching records. I have products table having columns like :- id, product_name

id   product_name
 1    woodpecker handpiece
 2    hand piece

Now I when search with query="woo" it's searching fine. When I add query="wooo" Now next time if I search query="woooooooo" its fine if user enters many o's. I want at least if user enters by mistake 2(o's) then it must search.

SELECT * FROM products WHERE product_name LIKE '%woo%'; //its working fine

SELECT * FROM products WHERE product_name LIKE '%woooo%'; //its not working fine.

My client gives some reference site where its working I am not sure how the doing. This reference image :-

enter image description here

See above image I have entered wrong query 'wooo'.. its still searching Please help me thanks in advance.

PrakashG
  • 1,642
  • 5
  • 20
  • 30
kunal
  • 255
  • 3
  • 17
  • Possible duplicate: https://stackoverflow.com/questions/4671378/levenshtein-mysql-php – Dharman Apr 17 '19 at 12:05
  • 1
    use `soundex()`; – Nikhil S Apr 17 '19 at 12:06
  • soundex not working – kunal Apr 17 '19 at 12:07
  • @Dharman its showing FUNCTION mydatabase.levenshtein does not exist – kunal Apr 17 '19 at 12:08
  • That is because you haven't read the answer in full... – Dharman Apr 17 '19 at 12:08
  • @Dharman how to add this code and where can you help me? – kunal Apr 17 '19 at 12:13
  • The reason why I linked it as duplicate is because it is already explained there in details and I didn't want to waste time to explain it again. `SOUNDEX` should suffice, but if it is not enough follow the linked guide. – Dharman Apr 17 '19 at 12:14
  • @Dharman when i am importing file its showing this error :- You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '' at line 5 – kunal Apr 17 '19 at 12:23
  • Did you change the delimiters? `DELIMITER @` and then back to `;` – Dharman Apr 17 '19 at 12:25
  • i copy and paste the first funtion in one sql fie http://www.artfulsoftware.com/infotree/qrytip.php?id=552 – kunal Apr 17 '19 at 12:27
  • Wrap that code with the `DELIMITER` statements. Put `DELIMITER @` before and put `DELIMITER ;` after. If you can't make this work, google how to create stored procedures in mysql – Dharman Apr 17 '19 at 12:39
  • @Dharman i have successfully imported the sql query but query runs fine but result is not coming.. can you help me? – kunal Apr 18 '19 at 04:36
  • My sql query is SELECT `product_name` FROM `products` WHERE levenshtein('wooo', 'product_name') BETWEEN 0 AND 4 but not returning data – kunal Apr 18 '19 at 04:37

1 Answers1

4

this will work:

select * from table1 where soundex(product_name)=soundex('wooooooodpecker handpiece');

http://sqlfiddle.com/#!9/59e340/4

One of the many MySQL string functions is the SOUNDEX() function. This function returns a Soundex string from a given string. If two words sound the same, they should have the same Soundex string. If two words sound similar, but not exactly the same, their Soundex string might look similar but not exactly the same.

is this what you want:

select * from table1 where 
soundex(substring(product_name,1,3))=soundex(substring('woooooo',1,3));

check;http://sqlfiddle.com/#!9/59e340/22

Nikhil S
  • 3,786
  • 4
  • 18
  • 32