SELECT query using <> - 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

SELECT query using <>

Author

Topic

OldMySQLUser
Constraint Violating Yak Guru

301 Posts

Posted - 2007-09-26 : 09:17:36

I wish to select all the records from a table where the field invoice_status does not contain 'POSTED'.

I tried:

SELECT * FROM myTable WHERE (invoice_status <> 'POSTED') ORDER BY date_received

but it returned an empty dataset. Examining the table with MS SQL Server Management Studio showed that only 1 record had 'POSTED' in the invoice_status field, so all the other records should have been returned.

What did I do wrong please?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-26 : 09:21:47

Are you using a CASE SenSiTIve collation?

E 12°55'05.25"
N 56°04'39.16"

X002548
Not Just a Number

15586 Posts

Posted - 2007-09-26 : 09:21:51

WHERE Invoice_Status NOT LIKE '%POSTED%'

????

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-26 : 09:25:53

SELECT distinct invoice_status FROM mytable where charindex(char(160), invoice_status) > 0

E 12°55'05.25"
N 56°04'39.16"

Kristen
Test

22859 Posts

Posted - 2007-09-26 : 09:42:46

Or just trailing spaces if CHAR? (or are they removed before a <> comparison ... haven't used CHAR datatype for so long I can't remember!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-26 : 10:44:15

Trailing spaces (CHAR 32) is not compared, so they shuold work.

DECLARE	@Sample TABLE (data CHAR(20))

INSERT	@Sample
SELECT	'Peso' UNION ALL
SELECT	'Peso   '

SELECT	data,
	'_' + data + '_' as stored,
	case when data <> 'peso' then 1 else 0 end as [<> Peso],
	case when data = 'peso' then 1 else 0 end as [= Peso],
	case when data = 'peso ' then 1 else 0 end as [= Peso<space>],
	case when data <> 'peso ' then 1 else 0 end as [<> Peso<space>]
FROM	@Sample

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources