problem with substring in mysql The 2019 Stack Overflow Developer Survey Results Are In ...
Grover's algorithm - DES circuit as oracle?
Is it ethical to upload a automatically generated paper to a non peer-reviewed site as part of a larger research?
How can I protect witches in combat who wear limited clothing?
What aspect of planet Earth must be changed to prevent the industrial revolution?
In horse breeding, what is the female equivalent of putting a horse out "to stud"?
Does Parliament hold absolute power in the UK?
how can a perfect fourth interval be considered either consonant or dissonant?
Can the DM override racial traits?
Why is superheterodyning better than direct conversion?
Can a 1st-level character have an ability score above 18?
Segmentation fault output is suppressed when piping stdin into a function. Why?
Windows 10: How to Lock (not sleep) laptop on lid close?
Was credit for the black hole image misattributed?
Finding degree of a finite field extension
Can a novice safely splice in wire to lengthen 5V charging cable?
Why can't devices on different VLANs, but on the same subnet, communicate?
Didn't get enough time to take a Coding Test - what to do now?
The following signatures were invalid: EXPKEYSIG 1397BC53640DB551
Can the prologue be the backstory of your main character?
How is simplicity better than precision and clarity in prose?
Create an outline of font
Why did all the guest students take carriages to the Yule Ball?
How to delete random line from file using Unix command?
What can I do if neighbor is blocking my solar panels intentionally?
problem with substring in mysql
The 2019 Stack Overflow Developer Survey Results Are In
Announcing the arrival of Valued Associate #679: Cesar Manara
Planned maintenance scheduled April 17/18, 2019 at 00:00UTC (8:00pm US/Eastern)Product Attribute List Design PatternMySQL warnings while loading integer data into table (SOLVED)How to simulate a table join on itself?Subquery ORDER BY doesn't work on MySQL 5.6, but works on 5.5How can I improve my product facet search database model (EAV)Update mysql DB when data has a single quote with Dynamic valuesCreate products with color variantsMySQL converting column of type TEXT to TINYTEXT truncates value at non-intuitive position, why?Importing True/False as tinyint failing when last fieldProblem with multiple 'where' for same table
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ margin-bottom:0;
}
I have simple EAV like database which has 3 tables: products, attributes, attribute_values.
I am using below code to find all products that have attribute 'length' and then I am using substring function to get rid of 'inch' and after that I am using between to get all products that have 'length' between 0 and 5.
SELECT products.name,products.id
FROM products
JOIN attribute_value_product on products.id = attribute_value_product.product_id
JOIN attribute_values on attribute_value_product.attribute_value_id = attribute_values.id
JOIN attributes on attribute_values.attribute_id = attributes.id AND attributes.name = 'length'
WHERE substring_index(attribute_values.value,'inch',1) + 0 BETWEEN 0 AND 5
Everything works fine however I am getting plenty of warnings:
Warning: #1292 Truncated incorrect DOUBLE value: 'black'
Warning: #1292 Truncated incorrect DOUBLE value: 'phat farm'
Warning: #1292 Truncated incorrect DOUBLE value: '2 kg'
Warning: #1292 Truncated incorrect DOUBLE value: 'roca wear'
it looks to me like that substring is going through all attribute_values in the database, even those that are not reffering to attribute 'length' - is it possible to change that ?
mysql substring
asked 10 mins ago
neocrkneocrk
1
add a comment |
I have simple EAV like database which has 3 tables: products, attributes, attribute_values.
I am using below code to find all products that have attribute 'length' and then I am using substring function to get rid of 'inch' and after that I am using between to get all products that have 'length' between 0 and 5.
SELECT products.name,products.id
FROM products
JOIN attribute_value_product on products.id = attribute_value_product.product_id
JOIN attribute_values on attribute_value_product.attribute_value_id = attribute_values.id
JOIN attributes on attribute_values.attribute_id = attributes.id AND attributes.name = 'length'
WHERE substring_index(attribute_values.value,'inch',1) + 0 BETWEEN 0 AND 5
Everything works fine however I am getting plenty of warnings:
Warning: #1292 Truncated incorrect DOUBLE value: 'black'
Warning: #1292 Truncated incorrect DOUBLE value: 'phat farm'
Warning: #1292 Truncated incorrect DOUBLE value: '2 kg'
Warning: #1292 Truncated incorrect DOUBLE value: 'roca wear'
it looks to me like that substring is going through all attribute_values in the database, even those that are not reffering to attribute 'length' - is it possible to change that ?
mysql substring
asked 10 mins ago
neocrkneocrk
1
add a comment |
I have simple EAV like database which has 3 tables: products, attributes, attribute_values.
I am using below code to find all products that have attribute 'length' and then I am using substring function to get rid of 'inch' and after that I am using between to get all products that have 'length' between 0 and 5.
SELECT products.name,products.id
FROM products
JOIN attribute_value_product on products.id = attribute_value_product.product_id
JOIN attribute_values on attribute_value_product.attribute_value_id = attribute_values.id
JOIN attributes on attribute_values.attribute_id = attributes.id AND attributes.name = 'length'
WHERE substring_index(attribute_values.value,'inch',1) + 0 BETWEEN 0 AND 5
Everything works fine however I am getting plenty of warnings:
Warning: #1292 Truncated incorrect DOUBLE value: 'black'
Warning: #1292 Truncated incorrect DOUBLE value: 'phat farm'
Warning: #1292 Truncated incorrect DOUBLE value: '2 kg'
Warning: #1292 Truncated incorrect DOUBLE value: 'roca wear'
it looks to me like that substring is going through all attribute_values in the database, even those that are not reffering to attribute 'length' - is it possible to change that ?
mysql substring
asked 10 mins ago
neocrkneocrk
1
I have simple EAV like database which has 3 tables: products, attributes, attribute_values.
I am using below code to find all products that have attribute 'length' and then I am using substring function to get rid of 'inch' and after that I am using between to get all products that have 'length' between 0 and 5.
SELECT products.name,products.id
FROM products
JOIN attribute_value_product on products.id = attribute_value_product.product_id
JOIN attribute_values on attribute_value_product.attribute_value_id = attribute_values.id
JOIN attributes on attribute_values.attribute_id = attributes.id AND attributes.name = 'length'
WHERE substring_index(attribute_values.value,'inch',1) + 0 BETWEEN 0 AND 5
Everything works fine however I am getting plenty of warnings:
Warning: #1292 Truncated incorrect DOUBLE value: 'black'
Warning: #1292 Truncated incorrect DOUBLE value: 'phat farm'
Warning: #1292 Truncated incorrect DOUBLE value: '2 kg'
Warning: #1292 Truncated incorrect DOUBLE value: 'roca wear'
it looks to me like that substring is going through all attribute_values in the database, even those that are not reffering to attribute 'length' - is it possible to change that ?
mysql substring
mysql substring
asked 10 mins ago
neocrkneocrk
1
asked 10 mins ago
neocrkneocrk
1
asked 10 mins ago
neocrkneocrk
1
asked 10 mins ago
neocrkneocrk
1
asked 10 mins ago
neocrkneocrk
1
1
add a comment |
add a comment |
0
active
oldest
votes
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
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: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
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%2fdba.stackexchange.com%2fquestions%2f234735%2fproblem-with-substring-in-mysql%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
0
active
oldest
votes
0
active
oldest
votes
active
oldest
votes
active
oldest
votes
Thanks for contributing an answer to Database Administrators Stack Exchange!
- 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%2fdba.stackexchange.com%2fquestions%2f234735%2fproblem-with-substring-in-mysql%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
