SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 SELECT query using <>		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

OldMySQLUser
Constraint Violating Yak Guru

301 Posts
Posted - 09/26/2007 :  09:17:36  Show Profile  Reply with Quote
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

Sweden
29138 Posts
Posted - 09/26/2007 :  09:21:47  Show Profile  Visit SwePeso's Homepage  Reply with Quote
Are you using a CASE SenSiTIve collation?


E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

X002548
Not Just a Number

15586 Posts
Posted - 09/26/2007 :  09:21:51  Show Profile  Reply with Quote
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
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29138 Posts
Posted - 09/26/2007 :  09:25:53  Show Profile  Visit SwePeso's Homepage  Reply with Quote
SELECT distinct invoice_status FROM mytable where charindex(char(160), invoice_status) > 0


E 12°55'05.25"
N 56°04'39.16"
Go to Top of Page

Kristen
Test

United Kingdom
22191 Posts
Posted - 09/26/2007 :  09:42:46  Show Profile  Reply with Quote
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!
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
29138 Posts
Posted - 09/26/2007 :  10:44:15  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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"
Edited by - SwePeso on 09/26/2007 10:47:40
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000