Clarification on NULL values in the records.

Hi All
Please write me the reason for
SQL:
--
select * from table1
where Required <> 'Y'
the result set is empty.
Table Schema is:
--
CREATE TABLE [dbo].[Table1] (
[Names] [nvarchar] (50) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL ,
[Required] [nvarchar] (10) COLLATE
SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO
Insert Statement is is:
--
insert into table1 ([Names],Required)
values('Test1',null)
insert into table1 ([Names],Required)
values('Test2','Y')
insert into table1 ([Names],Required)
values('Test3',null)
insert into table1 ([Names],Required)
values('Test4','Y')
insert into table1 ([Names],Required)
values('Test5',null)
Is there any option I need to get the appropriate result
set?
B'coz my result set expected is
Test1 NULL
Test3 NULL
Test5 NULLWhy are you allowing NULLs? That's the problem, you can't say NULL is not
equal to 'Y'... since the definition of NULL is unknown, then it very may
well by 'Y' ...
How about making it NOT NULL DEFAULT 'N'?
Or make your query WHERE COALESCE(Required, 'N') = 'N'?
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Ana" <anonymous@.discussions.microsoft.com> wrote in message
news:a96401c43652$5032ef30$a601280a@.phx.gbl...
> Hi All
> Please write me the reason for
> SQL:
> --
> select * from table1
> where Required <> 'Y'
> the result set is empty.
> Table Schema is:
> --
> CREATE TABLE [dbo].[Table1] (
> [Names] [nvarchar] (50) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL ,
> [Required] [nvarchar] (10) COLLATE
> SQL_Latin1_General_CP1_CI_AS NULL
> ) ON [PRIMARY]
> GO
> Insert Statement is is:
> --
> insert into table1 ([Names],Required)
> values('Test1',null)
> insert into table1 ([Names],Required)
> values('Test2','Y')
> insert into table1 ([Names],Required)
> values('Test3',null)
> insert into table1 ([Names],Required)
> values('Test4','Y')
> insert into table1 ([Names],Required)
> values('Test5',null)
> Is there any option I need to get the appropriate result
> set?
> B'coz my result set expected is
> Test1 NULL
> Test3 NULL
> Test5 NULL|||Hi Aaron Bertrand
Thanks for your clear information.Now I undestand.
Thanks once again
Ana.
>--Original Message--
>Why are you allowing NULLs? That's the problem, you
can't say NULL is not
>equal to 'Y'... since the definition of NULL is unknown,
then it very may
>well by 'Y' ...
>How about making it NOT NULL DEFAULT 'N'?
>Or make your query WHERE COALESCE(Required, 'N') = 'N'?
>--
>Aaron Bertrand
>SQL Server MVP
>http://www.aspfaq.com/
>
>
>"Ana" <anonymous@.discussions.microsoft.com> wrote in
message
>news:a96401c43652$5032ef30$a601280a@.phx.gbl...
>> Hi All
>> Please write me the reason for
>> SQL:
>> --
>> select * from table1
>> where Required <> 'Y'
>> the result set is empty.
>> Table Schema is:
>> --
>> CREATE TABLE [dbo].[Table1] (
>> [Names] [nvarchar] (50) COLLATE
>> SQL_Latin1_General_CP1_CI_AS NULL ,
>> [Required] [nvarchar] (10) COLLATE
>> SQL_Latin1_General_CP1_CI_AS NULL
>> ) ON [PRIMARY]
>> GO
>> Insert Statement is is:
>> --
>> insert into table1 ([Names],Required)
>> values('Test1',null)
>> insert into table1 ([Names],Required)
>> values('Test2','Y')
>> insert into table1 ([Names],Required)
>> values('Test3',null)
>> insert into table1 ([Names],Required)
>> values('Test4','Y')
>> insert into table1 ([Names],Required)
>> values('Test5',null)
>> Is there any option I need to get the appropriate result
>> set?
>> B'coz my result set expected is
>> Test1 NULL
>> Test3 NULL
>> Test5 NULL
>
>.
>|||Answered in .programming.
Please don't multi-post.
--
David Portas
SQL Server MVP
--sqlsql

check value in table upper case or lower

hi

i want to select * from table1 where name =petter?

now if there is many type of petter in table linke PETTER ,Petter Andpetter which record will come in display?

if i want all this three (PETTER,Petter,petter) will come in display which command is for this ?

regard

It depends on the column collation(default the same as database) setting, which you can check usingsyscolumns. If you want to ignore case, you can change all data into UPPER (or LOWER) case:

DECLARE @.n1 varchar(20)
SELECT @.n1='petter'
SELECT * FROM testCol where UPPER(name)=UPPER(@.n1)

|||

Or you can specify the collation used in the SELECT command:

DECLARE @.n1 varchar(20)
SELECT @.n1='petter'
SELECT * FROM testCol wherename=@.n1
collate SQL_Latin1_General_CP1_CI_AS

You can get descriptions of SQL collations using this statement:

SELECT * FROM ::fn_helpcollations()

|||

that was a good solution

but what it does

collate SQL_Latin1_General_CP1_CI_AS

any ideas

|||

This is used to specify collation used in the SELECT command. I choose SQL_Latin1_General_CP1_CI_AS because this collation is case insensitive (notice the CI). For more information, you can refer to:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ca-co_5ell.asp