in sql how can i exclude several values in a "not in" ?

Hello,

i'm trying to figure out how to exclude several values like follow :

Select
...
and LogonName NOT IN (N'[admin]*', N'Admin%', N'xe%', N'ext%', N'te-%')
and ....

but i'm receiving values like
LogonName : Admin....


how can i exclude in a list like in my example ?`

thank you in advance for your help.

Erwin
Erwin PombettAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PortletPaulEE Topic AdvisorCommented:
is the collation for LogonName case sensitive?

perhaps you need to apply a case insensitive collation

least desirable, but useful as a test:

and LOWER(LogonName) NOT IN (N'[admin]*', N'admin%', N'xe%', N'ext%', N'te-%')
Guy Hengel [angelIII / a3]Billing EngineerCommented:
I presume you want to have the actually a NOT IN LIKE ( ... ) ?

in which case, you cannot use NOT IN, you have to use:
and NOT LogonName LIKE N'[admin]*' 
and NOT LogonName LIKE N'Admin%'
and NOT LogonName LIKE N'xe%'
and NOT LogonName LIKE N'ext%'
and NOT LogonName LIKE N'te-%'

Open in new window


OR, you create a table with the patters to exclude:
create table SHORT_LOGON_NAME_EXCLUSIONS ( pattern nvarchar(100) )

insert into SHORT_LOGON_NAME_EXCLUSIONS values ( N'Admin%' )
insert into SHORT_LOGON_NAME_EXCLUSIONS values ( N'xe%' )
insert into SHORT_LOGON_NAME_EXCLUSIONS values ( N'ext%' )
insert into SHORT_LOGON_NAME_EXCLUSIONS values ( N'te-%'  )

Open in new window



and your code becomes like this:
and NOT EXISTS ( SELECT NULL FROM SHORT_LOGON_NAME_EXCLUSIONS
   WHERE  LogonName LIKE pattern )

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PortletPaulEE Topic AdvisorCommented:
Oh dear,  my comment is so wrong.  Please ignore it.
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Erwin PombettAuthor Commented:
hello Guy Angel and PortletPaul,

thank you for your replies.

Guy Angel, i 'm trying to figure out what "select null " is for  ? could you explain in short answer ?

thank you in advance.

toshi
Erwin PombettAuthor Commented:
Guy !
ok , i think i understand, we select NULL (an empty row) if the pattern is in logonName ?  
but the "not exists () " ? ....i can not figure out :/

Toshi
Guy Hengel [angelIII / a3]Billing EngineerCommented:
a NOT EXISTS ( subquery ) does not need any data to be returned, actually, hence SELECT NULL.
you could write SELECT 1, SELECT somecolumn , it will not change the behavior

the subquery here is "corelated", means that for each row from the main result set (in this case, the table that has the login names you want to show), the subquery will be run

 WHERE  LogonName LIKE pattern

it would be easier to understand if we write like this:
SELECT t.LogonName 
  FROM yourtable t
 WHERE NOT EXISTS ( SELECT NULL FROM SHORT_LOGON_NAME_EXCLUSIONS ex
   WHERE  t.LogonName LIKE ex.pattern )

Open in new window


hope this makes it clearer on what is happening
Erwin PombettAuthor Commented:
thanks a lot Guy !
I got it.
Toshi
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.