Opposite of Condition - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Opposite of Condition

Author

Topic

Dasman
Yak Posting Veteran

79 Posts

Posted - 2014-03-18 : 20:04:54

I have 91 records in a database but this query below yields 90:

Select * From TableName
Where CustomerName LIKE "% %";

This returns all but one customer name that two worded names like Jim Smith.

Can I do a opposite of this query to find the one customer that only has one word in his name?

In general my more conceptual question is: can I run a query to find the opposite of my condition. or to reveal the records that dont fufill a condition?

Thanks for your time in reading this post! :)

==========================
Pain is Weakness Leaving the Body.

nagino
Yak Posting Veteran

75 Posts

Posted - 2014-03-18 : 20:40:20

Do you mean following?

SELECT *
FROM TableName
WHERE CustomerName NOT LIKE '% %'
--NOT means opposite

or

SELECT *
FROM TableName
WHERE PATINDEX('% %', CustomerName) = 0
--PATINDEX() = 0 means NOT MATCH

#CAUTION : NULL record(s) cannot find each queries, so not completery opposite.
If you need that, CustomerName column is nullable, add clause to queries like following.

SELECT *
FROM TableName
WHERE CustomerName NOT LIKE '% %'
OR CustomerName IS NULL

SELECT *
FROM TableName
WHERE PATINDEX('% %', CustomerName) = 0
OR CustomerName IS NULL

-------------------------------------
From Japan
Sorry, my English ability is limited.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources