CODEWITHSUNDEEP
sqlsql-server
Shahab Haidar
Shahab Haidar

Reputation: 641

SQL Server wild card issue

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

Answers (5)

Ilyes
Ilyes

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

Michał Turczyn
Michał Turczyn

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

Marc Guillot
Marc Guillot

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

KtX2SkD
KtX2SkD

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

Aswani Madhavan
Aswani Madhavan

Reputation: 816

you can use IN

SELECT * FROM Calendar WHERE REMARK NOT IN('Saturday', 'Sunday')

Upvotes: 2

Related Questions