Reputation: 641
I have a Table (Calendar)
Date Remark
2010-01-01 Sunday
2010-02-01 Saturday
2010-03-01 Public Holiday
2010-03-01 Rose Day
2010-04-01 Valentines day
2010-04-01 Public Holiday
2010-01-01 Sunday
2010-02-01 Saturday
I want to select only those date which is not having Saturday and Sunday.
SELECT * FROM CALANDER WHERE REMARK NOT LIKE 'SATURDAY' OR Remark Not like 'Sunday'
I am executing this query but this doesn't help me.
Upvotes: 0
Views: 72
Reputation: 14926
Just use AND
instead of OR
:
CREATE TABLE MyDates (
[Date] DATE,
Remark NVARCHAR(50)
);
INSERT INTO MyDates VALUES
('2010-01-01', 'Sunday'),
('2010-02-01', 'Saturday'),
('2010-03-01', 'Public Holiday'),
('2010-03-01', 'Rose Day'),
('2010-04-01', 'Valentines day'),
('2010-04-01', 'Public Holiday'),
('2010-01-01', 'Sunday'),
('2010-02-01', 'Saturday');
SELECT *
FROM MyDates
WHERE Remark NOT LIKE 'Saturday' AND Remark Not like 'Sunday';
--Or Remark <> 'Saturday' AND Remark <> 'Sunday';
Results:
+---------------------+----------------+
| Date | Remark |
+---------------------+----------------+
| 01.03.2010 00:00:00 | Public Holiday |
| 01.03.2010 00:00:00 | Rose Day |
| 01.04.2010 00:00:00 | Valentines day |
| 01.04.2010 00:00:00 | Public Holiday |
+---------------------+----------------+
Checkout the demo
Upvotes: 1
Reputation: 37525
Well, if you want to work with regular expressions (wildcards) you should follow this approach:
Sunday and Saturdays are the only days that start with S
, so your wildcard should look like: 'S%day'
, in query:
SELECT * FROM Calendar WHERE REMARK LIKE 'S%day'
Upvotes: 1
Reputation: 6465
Your condition should be AND, not OR.
Also, if you want to use wildcards, the wildcard for any character is %.
These small modifications on your query will return all the rows not having Saturday and Sunday as part of their Remark column :
SELECT * FROM CALANDER WHERE REMARK NOT LIKE '%Saturday%' AND Remark Not like '%Sunday%'
Upvotes: 0
Reputation: 752
This could probably be written as a JOIN
too, dunno which's more efficient though:
SELECT *
FROM CALANDER
WHERE Date NOT IN (
SELECT DISTINCT Date
FROM CALANDER
WHERE Remark IN ('Saturday', 'Sunday')
)
Upvotes: 1
Reputation: 816
you can use IN
SELECT * FROM Calendar WHERE REMARK NOT IN('Saturday', 'Sunday')
Upvotes: 2