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.
mysql sql phpmyadmin wildcard
asked Nov 17 '18 at 7:02
zzzaaabbb
185
add a comment |
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.
mysql sql phpmyadmin wildcard
asked Nov 17 '18 at 7:02
zzzaaabbb
185
ah, ok. Sorry, I should have seen that. Rookie mistake. Thank you!
– zzzaaabbb
Nov 17 '18 at 7:04
1
You can take help from this url dev.mysql.com/doc/refman/8.0/en/pattern-matching.html
– Sadikhasan
Nov 17 '18 at 7:13
add a comment |
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.
mysql sql phpmyadmin wildcard
asked Nov 17 '18 at 7:02
zzzaaabbb
185
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.
mysql sql phpmyadmin wildcard
mysql sql phpmyadmin wildcard
asked Nov 17 '18 at 7:02
zzzaaabbb
185
asked Nov 17 '18 at 7:02
zzzaaabbb
185
asked Nov 17 '18 at 7:02
zzzaaabbb
185
asked Nov 17 '18 at 7:02
zzzaaabbb
185
asked Nov 17 '18 at 7:02
zzzaaabbb
185
185
ah, ok. Sorry, I should have seen that. Rookie mistake. Thank you!
– zzzaaabbb
Nov 17 '18 at 7:04
1
You can take help from this url dev.mysql.com/doc/refman/8.0/en/pattern-matching.html
– Sadikhasan
Nov 17 '18 at 7:13
add a comment |
ah, ok. Sorry, I should have seen that. Rookie mistake. Thank you!
– zzzaaabbb
Nov 17 '18 at 7:04
1
You can take help from this url dev.mysql.com/doc/refman/8.0/en/pattern-matching.html
– Sadikhasan
Nov 17 '18 at 7:13
ah, ok. Sorry, I should have seen that. Rookie mistake. Thank you!
– zzzaaabbb
Nov 17 '18 at 7:04
ah, ok. Sorry, I should have seen that. Rookie mistake. Thank you!
– zzzaaabbb
Nov 17 '18 at 7:04
1
1
You can take help from this url dev.mysql.com/doc/refman/8.0/en/pattern-matching.html
– Sadikhasan
Nov 17 '18 at 7:13
You can take help from this url dev.mysql.com/doc/refman/8.0/en/pattern-matching.html
– Sadikhasan
Nov 17 '18 at 7:13
add a comment |
1 Answer
1
active
oldest
votes
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.
answered Nov 17 '18 at 7:09
Madhur Bhaiya
19.5k62236
1
Not totally correct. The percent sign represents zero, one, or multiple characters. But this could depend on dialect.
– triplem
Nov 17 '18 at 7:18
@triplem . . . The definition of%does not depend on the database.
– Gordon Linoff
Nov 17 '18 at 12:38
I was unsure about this one. Thanks for the clarification.
– triplem
Nov 17 '18 at 12:40
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53349013%2fsql-wildcard-underscore-char-not-returning-results%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
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.
answered Nov 17 '18 at 7:09
Madhur Bhaiya
19.5k62236
1
Not totally correct. The percent sign represents zero, one, or multiple characters. But this could depend on dialect.
– triplem
Nov 17 '18 at 7:18
@triplem . . . The definition of%does not depend on the database.
– Gordon Linoff
Nov 17 '18 at 12:38
I was unsure about this one. Thanks for the clarification.
– triplem
Nov 17 '18 at 12:40
add a comment |
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.
answered Nov 17 '18 at 7:09
Madhur Bhaiya
19.5k62236
1
Not totally correct. The percent sign represents zero, one, or multiple characters. But this could depend on dialect.
– triplem
Nov 17 '18 at 7:18
@triplem . . . The definition of%does not depend on the database.
– Gordon Linoff
Nov 17 '18 at 12:38
I was unsure about this one. Thanks for the clarification.
– triplem
Nov 17 '18 at 12:40
add a comment |
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.
answered Nov 17 '18 at 7:09
Madhur Bhaiya
19.5k62236
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.
answered Nov 17 '18 at 7:09
Madhur Bhaiya
19.5k62236
edited Nov 17 '18 at 7:18
answered Nov 17 '18 at 7:09
Madhur Bhaiya
19.5k62236
answered Nov 17 '18 at 7:09
Madhur Bhaiya
19.5k62236
answered Nov 17 '18 at 7:09
Madhur Bhaiya
19.5k62236
19.5k62236
1
Not totally correct. The percent sign represents zero, one, or multiple characters. But this could depend on dialect.
– triplem
Nov 17 '18 at 7:18
@triplem . . . The definition of%does not depend on the database.
– Gordon Linoff
Nov 17 '18 at 12:38
I was unsure about this one. Thanks for the clarification.
– triplem
Nov 17 '18 at 12:40
add a comment |
1
Not totally correct. The percent sign represents zero, one, or multiple characters. But this could depend on dialect.
– triplem
Nov 17 '18 at 7:18
@triplem . . . The definition of%does not depend on the database.
– Gordon Linoff
Nov 17 '18 at 12:38
I was unsure about this one. Thanks for the clarification.
– triplem
Nov 17 '18 at 12:40
1
1
Not totally correct. The percent sign represents zero, one, or multiple characters. But this could depend on dialect.
– triplem
Nov 17 '18 at 7:18
Not totally correct. The percent sign represents zero, one, or multiple characters. But this could depend on dialect.
– triplem
Nov 17 '18 at 7:18
@triplem . . . The definition of
% does not depend on the database.– Gordon Linoff
Nov 17 '18 at 12:38
@triplem . . . The definition of
% does not depend on the database.– Gordon Linoff
Nov 17 '18 at 12:38
I was unsure about this one. Thanks for the clarification.
– triplem
Nov 17 '18 at 12:40
I was unsure about this one. Thanks for the clarification.
– triplem
Nov 17 '18 at 12:40
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53349013%2fsql-wildcard-underscore-char-not-returning-results%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
ah, ok. Sorry, I should have seen that. Rookie mistake. Thank you!
– zzzaaabbb
Nov 17 '18 at 7:04
1
You can take help from this url dev.mysql.com/doc/refman/8.0/en/pattern-matching.html
– Sadikhasan
Nov 17 '18 at 7:13