Adding a N to the front of a value in a query 1

[synapsevampire]

Using SQL Server 2000

I assume that some database setting is causing this, but when I issue a query of:

select directrep from table
where directrep <> 'Blah"

it eliminates the nulls for that column as well.

I test this by using:

select count(*)
from
(
select directrep from table
where directrep <> 'Blah"
) A
where directrep is null

And I get zero, but if I use:

select count(*) from table
where directrep is null

I get 300,000 rows.

Is there some setting which causes this odd behavior?

-k

 

[SQLDenis]

read this and run the examples

http://sqlservercode.blogspot.com/2006/01/null-trouble-in-sql-server-land.html

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[SQLSister]

I love the name of that article Dennis.

Questions about posting. See faq183-874

 

[SQLDenis]

Thanks

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[synapsevampire]

Interesting, but it doesn't quite resolve.

Any idea why the query gets preceded by a N, in the example of:

select directrep from table
where directrep <> N'Blah"

-k

 

[SQLDenis]

yes, you put N in front of unicode (nvarchar,nchar) values

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[SQLDenis]

Example

declare @v nvarchar(50)
select @v =N's'
select @v,datalength(@v)

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[synapsevampire]

I see, so it has no impact on the resultset, it's required for those data types?

How can I use a single query and filter only for specific values and NOT filter out the nulls?

Is there a setting?

-k

 

[SQLDenis]

where (directrep <> 'Blah'
or directrep is null)


Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[synapsevampire]

Ahhh, I see, so there isn't a way without explicitly stating to allow the nulls, thanks.

I had hoped there was a setting.

-k