SQL Like query for each word containing keyword

Question

E.g : I have 1 word for search the data. And the word is 'ayam'.

And this is my query :

select nama_product from product where nama_product like %a%

It works fine. But it show the record that i dont want to be showed. This is the result :

• ayam bakar
• daging ayam
• bayam hijau
• daun bayam

The first and the second result is okay. But the third and the last that i dont want to be showed.

Because i want to show 'ayam' not "b'ayam'"

How do i handle this? I don't know it is duplicate or not. Because i already search it but i didn't find.

Answer 1

Unfortunately this is a limitation of SQL. Some SQL databases have advanced text searching functions such as regular expressions which allow very specific text results.

For your case you will likely have to perform multiple LIKE conditions. For example:

select nama_product 
from product 
where 
    nama_product like 'ayam %'
    or nama_product like '% ayam %'
    or nama_product like '% ayam'
    or nama_product = 'ayam'

Please note that if the text fields are very large or if the table is large, queries that rely on LIKE operators can become very slow. LIKE does not scale well with large datasets. If this is a dataset you think will become very large in the future, best to design it in a way where the LIKE operator will not be needed.

Answer 2

If you need to search the word 'ayam' without any prefix and sufix character, you would better use regular expression for that .
e.g.,

SELECT namma_product FROM product WHERE namma_product REGEXP 'ayam'

try this. This will work

Answer 3

You can use regex to match only whole words:

select nama_product
  from product
 where nama_product regexp '(^| )ayam( |$)';

• (^| ) means the word must be the start of the string, or it must be preceded by a space
• ayam is the word we're matching
• ( |$) means the word must be followed by a space, or it must be the end of the string

SQL Fiddle

Answer 4

when u insert '%' before and after it mean All Character before(after) your keyword so just change how u want for example just keywords start with "ayam": select nama_product from product where nama_product like 'ayam%'

another example just keywords ended with "ayam": select nama_product from product where nama_product like '%ayam'

and after that look here ithink your answer is here :

Match only entire words with LIKE?