May 30, 2016 at 2:52 am
Good day Experts,
I have a table with the following columns Name , Memory, Model
I have the following data Caesar, xxxx xxxxx
Caesar(sql Vm), xxxx xxxxx
Caeser CrD, xxxxx xxxxx
Lebombo xxxx xxxxx
Maheema xxxx xxxxx
I want a query to select lebombo,maheema and caeser CRD only.When i use the NOT LIKE operator so that i can skip Caeser and Caeser(sql Vm) then CRD is not returned at all.
Please help
May 30, 2016 at 3:17 am
tmmutsetse (5/30/2016)
Good day Experts,I have a table with the following columns Name , Memory, Model
I have the following data Caesar, xxxx xxxxx
Caesar(sql Vm), xxxx xxxxx
Caeser CrD, xxxxx xxxxx
Lebombo xxxx xxxxx
Maheema xxxx xxxxx
I want a query to select lebombo,maheema and caeser CRD only.When i use the NOT LIKE operator so that i can skip Caeser and Caeser(sql Vm) then CRD is not returned at all.
Please help
Why not use IN?
Name IN ('Caeser CrD','Lebombo','Maheema')
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
May 30, 2016 at 3:22 am
Thanks Tom,Infact i have more than those names mentioned above.Almost 30 names.
May 30, 2016 at 4:52 am
tmmutsetse (5/30/2016)
Thanks Tom,Infact i have more than those names mentioned above.Almost 30 names.
Try to write down the definition of the logic, otherwise it will be impossible to construct a query to bring back the desired results.
😎
May 30, 2016 at 5:30 am
The query that i am using looks like the one below.
SELECT
Name,
Memory,
Model
FROM [xxxx].[dbo].[Status]
where
active = 1 and
and name not LIKE '%Caeser'
and name not like'%Caeser (sql VM)%';
I do no have so much experience in sql
May 30, 2016 at 7:46 am
is your name column Nullable and are there rows where it is null ?
ref: Something About Nothing: NULL By Kathi Kellenberger[/url]
ref: NULL Versus NULL? By Michael Coles[/url]
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
May 30, 2016 at 9:07 am
tmmutsetse (5/30/2016)
The query that i am using looks like the one below.SELECT
Name,
Memory,
Model
FROM [xxxx].[dbo].[Status]
where
active = 1 and
and name not LIKE '%Caeser'
and name not like'%Caeser (sql VM)%';
I do no have so much experience in sql
Here are some examples for you...
DECLARE @myTable TABLE (Name VARCHAR(20))
INSERT INTO @myTable
VALUES ('Caesar'), ('Caesar(sql Vm)'), ('Caesar CrD'), ('Lembombo'), ('Maheema')
--All Results
SELECT Name FROM @myTable
--Exclude Caesar & Caesar(sql Vm)
SELECT Name FROM @myTable WHERE Name NOT LIKE 'Caesar' AND Name NOT LIKE 'Caesar(sql Vm)'
--Alternatively (since we don't need to use wildcards)
SELECT Name FROM @myTable WHERE Name <> 'Caesar' AND Name <> 'Caesar(sql Vm)';
--This is much easier to maintain a list of names you want to exlcude
WITH myCTE AS
(
SELECT * FROM (VALUES ('Caesar'), ('Caesar(sql Vm)')) x(exName)
)
SELECT m.Name FROM @myTable m LEFT JOIN myCTE c ON c.exName = m.Name WHERE c.exName IS NULL
--You can also do it without a CTE
SELECT m.Name FROM @myTable m LEFT JOIN (VALUES ('Caesar'), ('Caesar(sql Vm)')) c(exName) ON c.exName = m.Name WHERE c.exName IS NULL
--And of course the easiest and most obvious way (stupid Monday)...thanks Eirikur
SELECT Name FROM @myTable WHERE Name NOT IN ('Caesar', 'Caesar(sql Vm)')
Of course you could also maintain an actual lookup table (blacklist) instead if it's warranted.
Whatever you do, you should NOT use something like this:
and name not LIKE '%Caeser'
The leading wildcard automatically makes it non-sargable which means you will have to do full table scans regardless of any indexes on your table.
Cheers,
May 30, 2016 at 9:32 am
tmmutsetse (5/30/2016)
The query that i am using looks like the one below.SELECT
Name,
Memory,
Model
FROM [xxxx].[dbo].[Status]
where
active = 1 and
and name not LIKE '%Caeser'
and name not like'%Caeser (sql VM)%';
I do no have so much experience in sql
What you posted is an attempt to code an undefined logic, not an expression of the logic.
😎
Writing the logic in pseudo code is often the best way of expressing it, for the example you have given it would be:
Select all rows
where the column Name does not contain "Ceasar" or "Caesar(sql Vm)".
The problem with this kind of logic is that there is no rule, only cherry picking into the exclusion set which means that all members of the exclusion set must be explicitly stated which also means that the only operator applicable is the equal operator, wild-cards and the like operators don't fit in.
This kind of queries is easy to write but harder to maintain, in your case the query is simply
SELECT
[Name]
,[Memory]
,[Model}
FROM [table_name]
WHERE [Name] NOT IN ('Ceasar','Caesar(sql Vm)');
May 30, 2016 at 9:39 am
Eirikur Eiriksson (5/30/2016)
tmmutsetse (5/30/2016)
The query that i am using looks like the one below.SELECT
Name,
Memory,
Model
FROM [xxxx].[dbo].[Status]
where
active = 1 and
and name not LIKE '%Caeser'
and name not like'%Caeser (sql VM)%';
I do no have so much experience in sql
What you posted is an attempt to code an undefined logic, not an expression of the logic.
😎
Writing the logic in pseudo code is often the best way of expressing it, for the example you have given it would be:
Select all rowswhere the column Name does not contain "Ceasar" or "Caesar(sql Vm)".
The problem with this kind of logic is that there is no rule, only cherry picking into the exclusion set which means that all members of the exclusion set must be explicitly stated which also means that the only operator applicable is the equal operator, wild-cards and the like operators don't fit in.
This kind of queries is easy to write but harder to maintain, in your case the query is simply
SELECT[Name]
,[Memory]
,[Model}
FROM [table_name]
WHERE [Name] NOT IN ('Ceasar','Caesar(sql Vm)');
LOL...how did I forget to include one of the most obvious example in my list. :crazy:
May 30, 2016 at 9:45 am
yb751 (5/30/2016)
Eirikur Eiriksson (5/30/2016)
tmmutsetse (5/30/2016)
The query that i am using looks like the one below.SELECT
Name,
Memory,
Model
FROM [xxxx].[dbo].[Status]
where
active = 1 and
and name not LIKE '%Caeser'
and name not like'%Caeser (sql VM)%';
I do no have so much experience in sql
What you posted is an attempt to code an undefined logic, not an expression of the logic.
😎
Writing the logic in pseudo code is often the best way of expressing it, for the example you have given it would be:
Select all rowswhere the column Name does not contain "Ceasar" or "Caesar(sql Vm)".
The problem with this kind of logic is that there is no rule, only cherry picking into the exclusion set which means that all members of the exclusion set must be explicitly stated which also means that the only operator applicable is the equal operator, wild-cards and the like operators don't fit in.
This kind of queries is easy to write but harder to maintain, in your case the query is simply
SELECT[Name]
,[Memory]
,[Model}
FROM [table_name]
WHERE [Name] NOT IN ('Ceasar','Caesar(sql Vm)');
LOL...how did I forget to include one of the most obvious example in my list. :crazy:
Not seeing the forest for the trees;-)
😎
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply