How to set where clause only if the value is not empty [duplicate]
This question already has an answer here:
Is storing a delimited list in a database column really that bad?
10 answers
I have to write a query where the where clause should work on only if the field value is not empty else all record should be fetched.
I have a table name post
id | valid_month | name |
------+-------------+------|
1 | 1,2,3 | post1|
2 | 1,2 | post2|
3 | | post3|
4 | 2,5 | post4|
5 | | post5|
----+-------------+------+
I want query like
select * from post where IF(valid_month != "") THEN valid_month REGEXP '[[:<:]]3[[:>:]]' END IF;
and result should like
id | valid_month | name |
------+-------------+------|
1 | 1,2,3 | post1|
3 | | post3|
5 | | post5|
----+-------------+------+
Is there any way to get this output in MySQL?
mysql sql select mysqli where-clause
edited Dec 1 '18 at 12:40
Cœur
19.1k9114155
asked Nov 26 '18 at 6:10
Harshwardhan SharmaHarshwardhan Sharma
1209
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 26 '18 at 6:47
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 have to write a query where the where clause should work on only if the field value is not empty else all record should be fetched.
I have a table name post
id | valid_month | name |
------+-------------+------|
1 | 1,2,3 | post1|
2 | 1,2 | post2|
3 | | post3|
4 | 2,5 | post4|
5 | | post5|
----+-------------+------+
I want query like
select * from post where IF(valid_month != "") THEN valid_month REGEXP '[[:<:]]3[[:>:]]' END IF;
and result should like
id | valid_month | name |
------+-------------+------|
1 | 1,2,3 | post1|
3 | | post3|
5 | | post5|
----+-------------+------+
Is there any way to get this output in MySQL?
mysql sql select mysqli where-clause
edited Dec 1 '18 at 12:40
Cœur
19.1k9114155
asked Nov 26 '18 at 6:10
Harshwardhan SharmaHarshwardhan Sharma
1209
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 26 '18 at 6:47
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 have to write a query where the where clause should work on only if the field value is not empty else all record should be fetched.
I have a table name post
id | valid_month | name |
------+-------------+------|
1 | 1,2,3 | post1|
2 | 1,2 | post2|
3 | | post3|
4 | 2,5 | post4|
5 | | post5|
----+-------------+------+
I want query like
select * from post where IF(valid_month != "") THEN valid_month REGEXP '[[:<:]]3[[:>:]]' END IF;
and result should like
id | valid_month | name |
------+-------------+------|
1 | 1,2,3 | post1|
3 | | post3|
5 | | post5|
----+-------------+------+
Is there any way to get this output in MySQL?
mysql sql select mysqli where-clause
edited Dec 1 '18 at 12:40
Cœur
19.1k9114155
asked Nov 26 '18 at 6:10
Harshwardhan SharmaHarshwardhan Sharma
1209
This question already has an answer here:
Is storing a delimited list in a database column really that bad?
10 answers
I have to write a query where the where clause should work on only if the field value is not empty else all record should be fetched.
I have a table name post
id | valid_month | name |
------+-------------+------|
1 | 1,2,3 | post1|
2 | 1,2 | post2|
3 | | post3|
4 | 2,5 | post4|
5 | | post5|
----+-------------+------+
I want query like
select * from post where IF(valid_month != "") THEN valid_month REGEXP '[[:<:]]3[[:>:]]' END IF;
and result should like
id | valid_month | name |
------+-------------+------|
1 | 1,2,3 | post1|
3 | | post3|
5 | | post5|
----+-------------+------+
Is there any way to get this output in MySQL?
This question already has an answer here:
Is storing a delimited list in a database column really that bad?
10 answers
mysql sql select mysqli where-clause
mysql sql select mysqli where-clause
edited Dec 1 '18 at 12:40
Cœur
19.1k9114155
asked Nov 26 '18 at 6:10
Harshwardhan SharmaHarshwardhan Sharma
1209
edited Dec 1 '18 at 12:40
Cœur
19.1k9114155
asked Nov 26 '18 at 6:10
Harshwardhan SharmaHarshwardhan Sharma
1209
edited Dec 1 '18 at 12:40
Cœur
19.1k9114155
edited Dec 1 '18 at 12:40
Cœur
19.1k9114155
edited Dec 1 '18 at 12:40
Cœur
19.1k9114155
19.1k9114155
asked Nov 26 '18 at 6:10
Harshwardhan SharmaHarshwardhan Sharma
1209
asked Nov 26 '18 at 6:10
Harshwardhan SharmaHarshwardhan Sharma
1209
asked Nov 26 '18 at 6:10
Harshwardhan SharmaHarshwardhan Sharma
1209
1209
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 26 '18 at 6:47
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 26 '18 at 6:47
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 |
add a comment |
2 Answers
2
active
oldest
votes
You could gain this behavior with the logical or operator:
SELECT *
FROM post
WHERE valid_month = '' OR valid_month REGEXP '[[:<:]]3[[:>:]]';
answered Nov 26 '18 at 6:14
MureinikMureinik
186k22138205
add a comment |
You can use the CASE as well
SELECT *
FROM post
WHERE (
CASE WHEN IFNULL(valid_month,'') != '' THEN
valid_month REGEXP '[[:<:]]3[[:>:]]'
ELSE 1 = 1
END);
answered Nov 26 '18 at 6:21
Vidhyut PandyaVidhyut Pandya
1,1981619
add a comment |
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
You could gain this behavior with the logical or operator:
SELECT *
FROM post
WHERE valid_month = '' OR valid_month REGEXP '[[:<:]]3[[:>:]]';
answered Nov 26 '18 at 6:14
MureinikMureinik
186k22138205
add a comment |
You could gain this behavior with the logical or operator:
SELECT *
FROM post
WHERE valid_month = '' OR valid_month REGEXP '[[:<:]]3[[:>:]]';
answered Nov 26 '18 at 6:14
MureinikMureinik
186k22138205
add a comment |
You could gain this behavior with the logical or operator:
SELECT *
FROM post
WHERE valid_month = '' OR valid_month REGEXP '[[:<:]]3[[:>:]]';
answered Nov 26 '18 at 6:14
MureinikMureinik
186k22138205
You could gain this behavior with the logical or operator:
SELECT *
FROM post
WHERE valid_month = '' OR valid_month REGEXP '[[:<:]]3[[:>:]]';
answered Nov 26 '18 at 6:14
MureinikMureinik
186k22138205
answered Nov 26 '18 at 6:14
MureinikMureinik
186k22138205
answered Nov 26 '18 at 6:14
MureinikMureinik
186k22138205
answered Nov 26 '18 at 6:14
MureinikMureinik
186k22138205
186k22138205
add a comment |
add a comment |
You can use the CASE as well
SELECT *
FROM post
WHERE (
CASE WHEN IFNULL(valid_month,'') != '' THEN
valid_month REGEXP '[[:<:]]3[[:>:]]'
ELSE 1 = 1
END);
answered Nov 26 '18 at 6:21
Vidhyut PandyaVidhyut Pandya
1,1981619
add a comment |
You can use the CASE as well
SELECT *
FROM post
WHERE (
CASE WHEN IFNULL(valid_month,'') != '' THEN
valid_month REGEXP '[[:<:]]3[[:>:]]'
ELSE 1 = 1
END);
answered Nov 26 '18 at 6:21
Vidhyut PandyaVidhyut Pandya
1,1981619
add a comment |
You can use the CASE as well
SELECT *
FROM post
WHERE (
CASE WHEN IFNULL(valid_month,'') != '' THEN
valid_month REGEXP '[[:<:]]3[[:>:]]'
ELSE 1 = 1
END);
answered Nov 26 '18 at 6:21
Vidhyut PandyaVidhyut Pandya
1,1981619
You can use the CASE as well
SELECT *
FROM post
WHERE (
CASE WHEN IFNULL(valid_month,'') != '' THEN
valid_month REGEXP '[[:<:]]3[[:>:]]'
ELSE 1 = 1
END);
answered Nov 26 '18 at 6:21
Vidhyut PandyaVidhyut Pandya
1,1981619
answered Nov 26 '18 at 6:21
Vidhyut PandyaVidhyut Pandya
1,1981619
answered Nov 26 '18 at 6:21
Vidhyut PandyaVidhyut Pandya
1,1981619
answered Nov 26 '18 at 6:21
Vidhyut PandyaVidhyut Pandya
1,1981619
1,1981619
add a comment |
add a comment |
