Database select statement, trying to use wildcards to display specific rows [duplicate]
This question already has an answer here:
Is storing a delimited list in a database column really that bad?
10 answers
I am trying to select all airlines where has the value 'Air Canada Rouge' for example, in the Airline columns there is sometimes concatenated values such as Air Canada, Air Canada Rouge, Air Transat. An example of the select statement I am currently using is:
select * from crs where Airline LIKE '%airlineName' OR Airline LIKE
'airlineName,%' OR Airline LIKE ',airlineName%';
This only works for rows that have Airlines that include 2 values such as Air Canada, Air Canada Rouge.
But I need the select statement to fetch rows with columns that has values that include 3 or 4 concatenated airlines.
How would I make a select statement to do this?
Thank you
mysql database
edited Nov 23 '18 at 15:54
TrebuchetMS
2,64811023
asked Nov 23 '18 at 15:48
bobb1213131bobb1213131
728
marked as duplicate by Strawberry mysql
Users with the mysql badge can single-handedly close mysql questions as duplicates and reopen them as needed.
StackExchange.ready(function() {
if (StackExchange.options.isMobile) return;
$('.dupe-hammer-message-hover:not(.hover-bound)').each(function() {
var $hover = $(this).addClass('hover-bound'),
$msg = $hover.siblings('.dupe-hammer-message');
$hover.hover(
function() {
$hover.showInfoMessage('', {
messageElement: $msg.clone().show(),
transient: false,
position: { my: 'bottom left', at: 'top center', offsetTop: -7 },
dismissable: false,
relativeToBody: true
});
},
function() {
StackExchange.helpers.removeMessages();
}
);
});
});
Nov 23 '18 at 16:16
This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.
add a comment |
This question already has an answer here:
Is storing a delimited list in a database column really that bad?
10 answers
I am trying to select all airlines where has the value 'Air Canada Rouge' for example, in the Airline columns there is sometimes concatenated values such as Air Canada, Air Canada Rouge, Air Transat. An example of the select statement I am currently using is:
select * from crs where Airline LIKE '%airlineName' OR Airline LIKE
'airlineName,%' OR Airline LIKE ',airlineName%';
This only works for rows that have Airlines that include 2 values such as Air Canada, Air Canada Rouge.
But I need the select statement to fetch rows with columns that has values that include 3 or 4 concatenated airlines.
How would I make a select statement to do this?
Thank you
mysql database
edited Nov 23 '18 at 15:54
TrebuchetMS
2,64811023
asked Nov 23 '18 at 15:48
bobb1213131bobb1213131
728
marked as duplicate by Strawberry mysql
Users with the mysql badge can single-handedly close mysql questions as duplicates and reopen them as needed.
StackExchange.ready(function() {
if (StackExchange.options.isMobile) return;
$('.dupe-hammer-message-hover:not(.hover-bound)').each(function() {
var $hover = $(this).addClass('hover-bound'),
$msg = $hover.siblings('.dupe-hammer-message');
$hover.hover(
function() {
$hover.showInfoMessage('', {
messageElement: $msg.clone().show(),
transient: false,
position: { my: 'bottom left', at: 'top center', offsetTop: -7 },
dismissable: false,
relativeToBody: true
});
},
function() {
StackExchange.helpers.removeMessages();
}
);
});
});
Nov 23 '18 at 16:16
This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.
1
As you want an exact matchwhere ',' + airline + ',' like '%,airlineName,%'- But you should normalize the data to one-item-per-row then this becomes trivial, in most cases delimited data in a column is a recipe for disaster.
– Alex K.
Nov 23 '18 at 15:55
Thanks that seems to work
– bobb1213131
Nov 23 '18 at 15:58
add a comment |
This question already has an answer here:
Is storing a delimited list in a database column really that bad?
10 answers
I am trying to select all airlines where has the value 'Air Canada Rouge' for example, in the Airline columns there is sometimes concatenated values such as Air Canada, Air Canada Rouge, Air Transat. An example of the select statement I am currently using is:
select * from crs where Airline LIKE '%airlineName' OR Airline LIKE
'airlineName,%' OR Airline LIKE ',airlineName%';
This only works for rows that have Airlines that include 2 values such as Air Canada, Air Canada Rouge.
But I need the select statement to fetch rows with columns that has values that include 3 or 4 concatenated airlines.
How would I make a select statement to do this?
Thank you
mysql database
edited Nov 23 '18 at 15:54
TrebuchetMS
2,64811023
asked Nov 23 '18 at 15:48
bobb1213131bobb1213131
728
This question already has an answer here:
Is storing a delimited list in a database column really that bad?
10 answers
I am trying to select all airlines where has the value 'Air Canada Rouge' for example, in the Airline columns there is sometimes concatenated values such as Air Canada, Air Canada Rouge, Air Transat. An example of the select statement I am currently using is:
select * from crs where Airline LIKE '%airlineName' OR Airline LIKE
'airlineName,%' OR Airline LIKE ',airlineName%';
This only works for rows that have Airlines that include 2 values such as Air Canada, Air Canada Rouge.
But I need the select statement to fetch rows with columns that has values that include 3 or 4 concatenated airlines.
How would I make a select statement to do this?
Thank you
This question already has an answer here:
Is storing a delimited list in a database column really that bad?
10 answers
mysql database
mysql database
edited Nov 23 '18 at 15:54
TrebuchetMS
2,64811023
asked Nov 23 '18 at 15:48
bobb1213131bobb1213131
728
edited Nov 23 '18 at 15:54
TrebuchetMS
2,64811023
asked Nov 23 '18 at 15:48
bobb1213131bobb1213131
728
edited Nov 23 '18 at 15:54
TrebuchetMS
2,64811023
edited Nov 23 '18 at 15:54
TrebuchetMS
2,64811023
edited Nov 23 '18 at 15:54
TrebuchetMS
2,64811023
2,64811023
asked Nov 23 '18 at 15:48
bobb1213131bobb1213131
728
asked Nov 23 '18 at 15:48
bobb1213131bobb1213131
728
asked Nov 23 '18 at 15:48
bobb1213131bobb1213131
728
728
marked as duplicate by Strawberry mysql
Users with the mysql badge can single-handedly close mysql questions as duplicates and reopen them as needed.
StackExchange.ready(function() {
if (StackExchange.options.isMobile) return;
$('.dupe-hammer-message-hover:not(.hover-bound)').each(function() {
var $hover = $(this).addClass('hover-bound'),
$msg = $hover.siblings('.dupe-hammer-message');
$hover.hover(
function() {
$hover.showInfoMessage('', {
messageElement: $msg.clone().show(),
transient: false,
position: { my: 'bottom left', at: 'top center', offsetTop: -7 },
dismissable: false,
relativeToBody: true
});
},
function() {
StackExchange.helpers.removeMessages();
}
);
});
});
Nov 23 '18 at 16:16
This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.
marked as duplicate by Strawberry mysql
Users with the mysql badge can single-handedly close mysql questions as duplicates and reopen them as needed.
StackExchange.ready(function() {
if (StackExchange.options.isMobile) return;
$('.dupe-hammer-message-hover:not(.hover-bound)').each(function() {
var $hover = $(this).addClass('hover-bound'),
$msg = $hover.siblings('.dupe-hammer-message');
$hover.hover(
function() {
$hover.showInfoMessage('', {
messageElement: $msg.clone().show(),
transient: false,
position: { my: 'bottom left', at: 'top center', offsetTop: -7 },
dismissable: false,
relativeToBody: true
});
},
function() {
StackExchange.helpers.removeMessages();
}
);
});
});
Nov 23 '18 at 16:16
This question has been asked before and already has an answer. If those answers do not fully address your question, please ask a new question.
1
As you want an exact matchwhere ',' + airline + ',' like '%,airlineName,%'- But you should normalize the data to one-item-per-row then this becomes trivial, in most cases delimited data in a column is a recipe for disaster.
– Alex K.
Nov 23 '18 at 15:55
Thanks that seems to work
– bobb1213131
Nov 23 '18 at 15:58
add a comment |
1
As you want an exact matchwhere ',' + airline + ',' like '%,airlineName,%'- But you should normalize the data to one-item-per-row then this becomes trivial, in most cases delimited data in a column is a recipe for disaster.
– Alex K.
Nov 23 '18 at 15:55
Thanks that seems to work
– bobb1213131
Nov 23 '18 at 15:58
1
1
As you want an exact match
where ',' + airline + ',' like '%,airlineName,%' - But you should normalize the data to one-item-per-row then this becomes trivial, in most cases delimited data in a column is a recipe for disaster.– Alex K.
Nov 23 '18 at 15:55
As you want an exact match
where ',' + airline + ',' like '%,airlineName,%' - But you should normalize the data to one-item-per-row then this becomes trivial, in most cases delimited data in a column is a recipe for disaster.– Alex K.
Nov 23 '18 at 15:55
Thanks that seems to work
– bobb1213131
Nov 23 '18 at 15:58
Thanks that seems to work
– bobb1213131
Nov 23 '18 at 15:58
add a comment |
1 Answer
1
active
oldest
votes
Use
select *
from crs
where Airline LIKE '%airlineName%';
answered Nov 23 '18 at 15:51
krish KMkrish KM
4,2021727
add a comment |
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
Use
select *
from crs
where Airline LIKE '%airlineName%';
answered Nov 23 '18 at 15:51
krish KMkrish KM
4,2021727
add a comment |
Use
select *
from crs
where Airline LIKE '%airlineName%';
answered Nov 23 '18 at 15:51
krish KMkrish KM
4,2021727
add a comment |
Use
select *
from crs
where Airline LIKE '%airlineName%';
answered Nov 23 '18 at 15:51
krish KMkrish KM
4,2021727
Use
select *
from crs
where Airline LIKE '%airlineName%';
answered Nov 23 '18 at 15:51
krish KMkrish KM
4,2021727
answered Nov 23 '18 at 15:51
krish KMkrish KM
4,2021727
answered Nov 23 '18 at 15:51
krish KMkrish KM
4,2021727
answered Nov 23 '18 at 15:51
krish KMkrish KM
4,2021727
4,2021727
add a comment |
add a comment |
1
As you want an exact match
where ',' + airline + ',' like '%,airlineName,%'- But you should normalize the data to one-item-per-row then this becomes trivial, in most cases delimited data in a column is a recipe for disaster.– Alex K.
Nov 23 '18 at 15:55
Thanks that seems to work
– bobb1213131
Nov 23 '18 at 15:58