Comparison to NULL and '' fields

Guys, can anybody remind the settings responsible for the results of the
search by the NULL fields? I saw this database setting several months ago
but forgot this switch. The problem is the following. We're trying to run a
search in a few tables using some SQL query with a multilevel combination of
AND/OR with
WHERE SOMEFIELD LIKE '%SOMETEXT%'
If the field is NULL or '' (zero length string) then two possible results
are available depending on this database setting, true or false. The default
setting is so that if the field is NULL these records are excluded from my
result list but I need all these records to be included. So I need to find
this switch to get TRUE always when I compare to NULL strings to include the
records with NULL fields into the result list.
Thanks,
Dmitri.Do you mean
SET ANSI_NULLS
Specifies SQL-92 compliant behavior of the Equals (=) and Not Equal to (<> )
comparison operators when used with null values.
Jens SUessmeyer.
"Just D." <no@.spam.please> schrieb im Newsbeitrag
news:Ojtbe.70654$lz2.58655@.fed1read07...
> Guys, can anybody remind the settings responsible for the results of the
> search by the NULL fields? I saw this database setting several months ago
> but forgot this switch. The problem is the following. We're trying to run
> a search in a few tables using some SQL query with a multilevel
> combination of AND/OR with
> WHERE SOMEFIELD LIKE '%SOMETEXT%'
> If the field is NULL or '' (zero length string) then two possible results
> are available depending on this database setting, true or false. The
> default setting is so that if the field is NULL these records are excluded
> from my result list but I need all these records to be included. So I need
> to find this switch to get TRUE always when I compare to NULL strings to
> include the records with NULL fields into the result list.
> Thanks,
> Dmitri.
>|||I strongly recommend not to do this. Take a look at this interesting puzzle
and see the consequnces of setting ANSI_NULLS OFF.
http://groups-beta.google.com/group...aca91a7912bdc76
To accomplish what you want, use:
...
WHERE SOMEFIELD LIKE '%SOMETEXT%' or soemfield is null
AMB
"Just D." wrote:

> Guys, can anybody remind the settings responsible for the results of the
> search by the NULL fields? I saw this database setting several months ago
> but forgot this switch. The problem is the following. We're trying to run
a
> search in a few tables using some SQL query with a multilevel combination
of
> AND/OR with
> WHERE SOMEFIELD LIKE '%SOMETEXT%'
> If the field is NULL or '' (zero length string) then two possible results
> are available depending on this database setting, true or false. The defau
lt
> setting is so that if the field is NULL these records are excluded from my
> result list but I need all these records to be included. So I need to find
> this switch to get TRUE always when I compare to NULL strings to include t
he
> records with NULL fields into the result list.
> Thanks,
> Dmitri.
>
>|||Hi Jens,
Probably you're right. But if it works with LIKE ? Or only with <> = ?
Thanks,
Dmitri.
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:OIOIIenSFHA.2136@.TK2MSFTNGP14.phx.gbl...
> Do you mean
> SET ANSI_NULLS
> Specifies SQL-92 compliant behavior of the Equals (=) and Not Equal to
> (<> ) comparison operators when used with null values.
> Jens SUessmeyer.
> "Just D." <no@.spam.please> schrieb im Newsbeitrag
> news:Ojtbe.70654$lz2.58655@.fed1read07...
>|||I think it only changes the behavior of comparisons that include
at least one literal or variable that is NULL. Column-to-column
comparisons between NULLs are never true, regardless of the
setting. I think <column> LIKE NULL is never true also.
set ansi_nulls off
go
declare @.t table (
a int,
c char
)
insert into @.t values (null, null)
insert into @.t values (0, '0')
select * from @.t
where a = null
select * from @.t
where a = c
select * from @.t
where c like null
go
set ansi_nulls on
Steve Kass
Drew University
Just D. wrote:

>Hi Jens,
>Probably you're right. But if it works with LIKE ? Or only with <> = ?
>Thanks,
>Dmitri.
>"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
>message news:OIOIIenSFHA.2136@.TK2MSFTNGP14.phx.gbl...
>
>
>|||Hi Steve,
My parameters that I provide to SQL are never NULL, the string should look
like this:
SELECT * FROM blablabla WHERE Param1 LIKE '%Col1%' OR Param2 LIKE '%Col2%'
OR Param3 LIKE '%Col3%'
This is a very simplified string because actually it's a very long
combination of OR/ANDs and braces. The Param# are never NULL, but if the
value in the column is NULL then I lose this record from the result and this
is a problem. If I had some global setting it would be nice but if I don't
have this way then I need to evaluate all these cells manually and it will
be nightmare because i need to search in 20-30 fields in a few tables, the
tables are long enough and not necessarily all these value are set to
something instead of NULL.
Dmitri.
"Steve Kass" <skass@.drew.edu> wrote in message
news:uABhz7rSFHA.3312@.TK2MSFTNGP12.phx.gbl...
>I think it only changes the behavior of comparisons that include
> at least one literal or variable that is NULL. Column-to-column
> comparisons between NULLs are never true, regardless of the
> setting. I think <column> LIKE NULL is never true also.
> set ansi_nulls off
> go
> declare @.t table (
> a int,
> c char
> )
> insert into @.t values (null, null)
> insert into @.t values (0, '0')
> select * from @.t
> where a = null
> select * from @.t
> where a = c
> select * from @.t
> where c like null
> go
> set ansi_nulls on
> Steve Kass
> Drew University
> Just D. wrote:
>|||> My parameters that I provide to SQL are never NULL, the string should look
> like this:
>
> SELECT * FROM blablabla WHERE Param1 LIKE '%Col1%' OR Param2 LIKE '%Col2%'
OR
> Param3 LIKE '%Col3%'
> This is a very simplified string because actually it's a very long combina
tion
> of OR/ANDs and braces. The Param# are never NULL, but if the value in the
> column is NULL then I lose this record from the result and this is a problem.[/col
or]
As I see it, there are a couple of solutions. The first, most obvious soluti
on
is to populate those nulls with something (e.g. an empty string (ugh) ) and
set
the column to be non-nullable. Then you won't have to worry about null colum
n
values.
The other solution is to change your where clause to account for nulls like
so
(depending on the actual logic desired):
Select F1...Fn
From Table
Where Param1 Is Null
Or Param1 Like '%Col1%'
Or Param2 Is Null
Or Param2 Like '%Col2%'
Or Param3 Is Null
Or Param3 Like '%Col3%'
> If I had some global setting it would be nice but if I don't have this way
> then I need to evaluate all these cells manually and it will be nightmare
> because i need to search in 20-30 fields in a few tables, the tables are l
ong
> enough and not necessarily all these value are set to something instead of
> NULL.
I don't see that you have a whole lot of choices. It sounds like populating
those fields with something would be your easiest route. I would definitely
not
recommend playing with the ANSI_NULLS option. It will confuse the hell out o
f
any other developer that looks at your code. It is one of those unobvious
"gotchas" that developers hate.
Thomas|||Dmitri,
What you want isn't going to be found in a setting,
if you want NULL LIKE '%Col1%' to be true.
If you want rows where any column is null, along with
the once returned by the query below, try
WHERE COALESCE(Param1,'Col1') LIKE '%Col1%'
OR COALESCE(Param2,'Col2') LIKE '%Col2%'
OR COALESCE(Param3,'Col3') LIKE '%Col3%'
It seems very strange to have columns named ParamX where you
search for values called Col1, so if this is not what you want, it
will be best if you provide sample table data that includes rows
you want (matching LIKE), rows you want (because of NULLs)
and some rows you don't want, so you can explain and show us
what you want to retrieve.
SK
Just D. wrote:

>Hi Steve,
>My parameters that I provide to SQL are never NULL, the string should look
>like this:
>
>SELECT * FROM blablabla WHERE Param1 LIKE '%Col1%' OR Param2 LIKE '%Col2%'
>OR Param3 LIKE '%Col3%'
>This is a very simplified string because actually it's a very long
>combination of OR/ANDs and braces. The Param# are never NULL, but if the
>value in the column is NULL then I lose this record from the result and thi
s
>is a problem. If I had some global setting it would be nice but if I don't
>have this way then I need to evaluate all these cells manually and it will
>be nightmare because i need to search in 20-30 fields in a few tables, the
>tables are long enough and not necessarily all these value are set to
>something instead of NULL.
>Dmitri.
>"Steve Kass" <skass@.drew.edu> wrote in message
>news:uABhz7rSFHA.3312@.TK2MSFTNGP12.phx.gbl...
>
>
>