T_SQL..I need the opposite results set..

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

T_SQL..I need the opposite results set..

Author

Topic

finnald0
Starting Member

2 Posts

Posted - 2009-07-08 : 07:16:23

Hi, total n00b here, wondering if someone can help me out with a query?

The following query returns some information from my results table where the staff number (staff_no is a collum) starts with a '1' and is followed by a letter, or starts with an 'X'

select * from tbl_results
where ((staff_no like '1%') and (ascii(substring(Staff_No,2,1)) between 65 and 90))
or staff_no like 'x%'

How do invert the query to return only the results from the table that don't match this criteria? (I want to return results where the staff number doesn't start with a 1, followed by a letter, or doesn't start with an X)

Many thanks!

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-07-08 : 07:25:42

Give this a try:
select * from table where staff_no like '1[A-Z]%' or staff_no like 'x%'

select * from table where staff_no not like '1[A-Z]%' and staff_no not like 'x%'

No, you're never too old to Yak'n'Roll if you're too young to die.