I have the following piece of code
cmd.CommandText = "SELECT * FROM product WHERE name LIKE '%@pattern%' OR description LIKE '%@pattern%' OR category LIKE '%@pattern%';";
cmd.Parameters.AddWithValue("pattern", pattern);
This returns an empty resultset in my code.
But if I type the query in PgAdmin like this
SELECT *
FROM product
WHERE name LIKE '%otter%'
OR description LIKE '%otter%'
OR category LIKE '%otter%';
What am I doing wrong?
You search for %@pattern% string literal. Use string concatentation:
cmd.CommandText = "SELECT * FROM product WHERE name LIKE '%' || @pattern || '%' OR description LIKE '%' || @pattern ||'%' OR category LIKE '%' || @pattern ||'%';";
cmd.Parameters.AddWithValue("pattern", pattern);
Anyway expression starting with wildcard is not SARGable so it will have poor performace. Consider using FULL TEXT INDEX.
When you write your query as:
cmd.CommandText = "SELECT * FROM product WHERE name LIKE '%@pattern%' OR description LIKE '%@pattern%' OR category LIKE '%@pattern%';";
You expect that @patern will be replaced with your value with second line. But it is not the case. What it really does is searching for string literal %@pattern% which of course you don't have in table. That is why you get empty resultset.
When you use string concatenation you will add wildcards (start/end) to your parameter's actual value.
