CODEWITHSUNDEEP
mysqlsqlphpmyadminwildcard
zzzaaabbb
zzzaaabbb

Reputation: 149

SQL wildcard underscore char not returning results

Anyone know why this query is returning zero results? It should be returning about 93 results.

SELECT ps_stock_available.id_product FROM ps_stock_available
LEFT JOIN ps_product_lang ON ps_stock_available.id_product = ps_product_lang.id_product
WHERE description LIKE 'Christma_';

This query also returns no results:

SELECT ps_stock_available.id_product FROM ps_stock_available
LEFT JOIN ps_product_lang ON ps_stock_available.id_product = ps_product_lang.id_product
WHERE description LIKE 'Christma%';

This query returns 93 results:

SELECT ps_stock_available.id_product FROM ps_stock_available
LEFT JOIN ps_product_lang ON ps_stock_available.id_product = ps_product_lang.id_product
WHERE description LIKE '%Christma%';

I am working in phpMyAdmin ver. 4.7.7 SQL query box of MySQL version 5.6.41-84.1.

Upvotes: 1

Views: 308

Answers (1)

Madhur Bhaiya
Madhur Bhaiya

Reputation: 28834

You possibly don't have any description field which has its value starting from Christma.

LIKE 'Christma%' and LIKE 'Christma_' expects the value to start from Christma with one or more characters on the right side of it. There should be no characters (no even whitespace characters) on the left side of the Christma. Also note that, _ wildcard expects exactly one character.

For eg, if description column has Christmas Carol value. LIKE 'Christma%' will match the value; while LIKE 'Christma_' will not, as it expects exactly one character only after Christma.

LIKE '%Christma%' expects zero or many character on the either sides of Christma, in order to be able to match.

From Docs:

SQL pattern matching enables you to use _ to match any single character and % to match an arbitrary number of characters (including zero characters). In MySQL, SQL patterns are case-insensitive by default.

Upvotes: 1

Related Questions