Charindex - 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

SQL Server 2000 Forums

SQL Server Development (2000)

Charindex

Author

Topic

anuradhay
Starting Member

41 Posts

Posted - 2004-10-12 : 01:07:28

if charindex(" ", null) != 0
select 1
else
select 0

SQL server return null when the 2nd arg in charindex is null.
If I execute the above query in SQL 2000 it should select 1. but it is selecting 0. But if i change the query like

declare @var int
select @var = null
if @var !=0
select 1
else
select 0

it is working fine. Why it is behaving like this?

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2004-10-12 : 01:13:30

coz charindex will tell the position of 2nd argument in first argument if it's found. you're looking for null in space or is that a ""?

if you want to check if the string you're passing is null stick to:
if @var is null

anuradhay
Starting Member

41 Posts

Posted - 2004-10-12 : 01:24:49

No.It will check for the 1st arg in the 2nd argument.

anuradhay
Starting Member

41 Posts

Posted - 2004-10-12 : 01:24:50

No.It will check for the 1st arg in the 2nd argument.

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2004-10-12 : 01:28:51

oopss sorry...
problem is you're looking for a space in null which will invoke an error (i ran your code).

anuradhay
Starting Member

41 Posts

Posted - 2004-10-12 : 02:09:05

It is not throwing any error for me.. have to check the settings in tools - options - connection properties

Kristen
Test

22859 Posts

Posted - 2004-10-12 : 04:03:25

Its not throwing an error for me either!

However:

declare @var int
select @var = null
if @var !=0
select 1
else
select 0

gives me 0 - are you saying you get 1 ??

I get 0 for all of the following:

SELECT charindex(' ', null)
-- = NULL

if charindex(' ', null) <> 0
select 1
else
select 0
-- = 0

IF NULL <> 0
select 1
else
select 0
-- = 0

declare @var int
select @var = null
if @var != 0
select 1
else
select 0
-- = 0

Kristen

anuradhay
Starting Member

41 Posts

Posted - 2004-10-12 : 04:37:53

declare @var int
select @var = null
if @var !=0
select 1
else
select 0

I get 1 for this which is correct.

if charindex(' ', null) <> 0
select 1
else
select 0
-- = 0

output for this should be 1. Pls set the following options and check.set ansi nulls off and set concat_null_yields_null off.

Kristen
Test

22859 Posts

Posted - 2004-10-12 : 05:33:53

set ansi_nulls OFF
set concat_null_yields_null OFF

gives me the same results.

With those two either both ON or both OFF the following gives me the same result - that's not correct is it?

set ansi_nulls ON
set concat_null_yields_null ON
set NOCOUNT ON

if charindex(' ', null) <> 0
select 1
else
select 0
-- = 0

if charindex(' ', null) = 0
select 1
else
select 0
-- = 0

Kristen

anuradhay
Starting Member

41 Posts

Posted - 2004-10-12 : 05:45:51

Yes. u r right. but my question is why it is going to the else block?
charindex('',null) is null. and null != 0 .so it should return 1 right??
if charindex(' ', null) <> 0
select 1
else
select 0
-- = 0

this query should return 1.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-10-12 : 09:08:54

charindex(Anything, NULL) should return NULL. NULL = unknown. If I tell you I am thinking of a word, but don't tell you word, and then I ask you: does it contain an "A" ? your answer: I don't know. Null.

As for why the ELSE block is being executed, remember: The IF is executed if the expression is TRUE, the ELSE is executed otherwise. Just as it says! And the two possibilities other than true? FALSE or ... Null!

Better yet, write your example like this:

select 1 where charindex(' ', Null) <> 0

this returns Nothing. Why? Because the condition is not true. it's Null. Yes, it's not FALSE, either, but it's not true.

One more example:

if charindex(' ', Null) <> 0 select 1
if not (charindex(' ', Null) <> 0) select 2

The two expressions are simply the opposite, so you would think that no matter what, one of them must be execute the select. Again, wrong -- there is a 3rd possibility, and that is the NULL posibility, and unless that is explicitly handled, it will either be a) evaluated as part of an ELSE when you don't expect it or b) not be evaluated at all.

So, you make sure you always anticipate NULLs and handle them properly:

if charindex(' ', ISNULL(@SomeValue, '')) <> 0 ....

- Jeff

Kristen
Test

22859 Posts

Posted - 2004-10-12 : 15:16:15

What I'm struggling to understand (but never thought to question it before) is that the first three pairs (below) return True/False for opposite tests, but the 4th one returns FALSE for BOTH tests - am I just being thick?

set ansi_nulls OFF
set concat_null_yields_null OFF
set NOCOUNT ON

SELECT CASE WHEN NULL = 0 THEN 'NULL=0 TRUE' ELSE 'NULL=0 FALSE' END
SELECT CASE WHEN NULL <> 0 THEN 'NULL<>0 TRUE' ELSE 'NULL<>0 FALSE' END

DECLARE @var int
SELECT @var = 0
SELECT CASE WHEN NULL = @var THEN 'NULL=@var TRUE' ELSE 'NULL=@var FALSE' END
SELECT CASE WHEN NULL <> @var THEN 'NULL<>@var TRUE' ELSE 'NULL<>@var FALSE' END

SELECT CASE WHEN charindex(' ', null) = NULL THEN 'NULL=charindex TRUE' ELSE 'NULL=charindex FALSE' END
SELECT CASE WHEN charindex(' ', null) <> NULL THEN 'NULL<>charindex TRUE' ELSE 'NULL<>charindex FALSE' END

-- Why are BOTH of these FALSE?
SELECT CASE WHEN charindex(' ', null) = 0 THEN 'charindex=0 TRUE' ELSE 'charindex=0 FALSE' END
SELECT CASE WHEN charindex(' ', null) <> 0 THEN 'charindex<>0 TRUE' ELSE 'charindex<>0 FALSE' END

Kristen

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-10-12 : 15:26:17

NEITHER is false. Both are something OTHER than TRUE.

Does that make sense?

Remember,

IF <cond> A ELSE B

Does not mean: IF <cond> is true, do A; if <cond> is false, do B

It means: IF <cond> is true, do A; otherwise do B.

Does this make sense? Again, same with a WHERE clause. It returns only rows in which the condition evaluates to TRUE. It does NOT exclude rows in which the condition evaluates to FALSE.

It's a key difference when you bring NULLs and unknown's into the equation.

- Jeff

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-10-12 : 15:39:46

I think what Kristen is saying is why is the comparison 'null<>0' not false. It does seem like null is something different than zero, BUT as Jeff said, null is "I don't know", thus it could be zero, but there is no way of knowing. Thats were the third option comes in true, false, and NULL.

Corey

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2004-10-12 : 15:43:20

Jeff, it looks wrong to me too. BOL is pretty definitive:
"When SET ANSI_NULLS is OFF, comparisons of all data against a null value evaluate to TRUE if the data value is NULL."
CHARINDEX(' ', NULL) seems to be behaving like some sort of super-NULL that can disregard the ANSI_NULL setting!
Ah, the perils of turning ANSI_NULL off!

Kristen
Test

22859 Posts

Posted - 2004-10-12 : 15:44:27

Yeah, I'm happy that I know what NULL is :-)

What I don't understand is why three tests for NULL=0 / NULL<>0 give different results - and in particular with SET ANSI_NULLS OFF which allows comparison between NULL and other stuff ... not that I do that ever, but I'm still curious to know why there is the inconsistency which anuradhay has found.

Kristen

Kristen

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-10-12 : 15:44:40

OOHHH ! ANSI NULLS is OFF ! sorry, all bets are off in that case. I would personally never use that option.

Now we know why !

- Jeff

Kristen
Test

22859 Posts

Posted - 2004-10-12 : 15:48:21

hehehe ... I'm in that boat too. But I reckon that SQL should be consistent. Perhaps its a bug <shrug/>

Kristen

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2004-10-12 : 15:58:10

Very strange...


SET ANSI_NULLS OFF

-- t f
SELECT
  CASE WHEN NULL <> 0 THEN 't' ELSE 'f' END AS ne,
  CASE WHEN NULL = 0 THEN 't' ELSE 'f' END AS eq

-- f f
SELECT
  CASE WHEN i <> 0 THEN 't' ELSE 'f' END AS ne,
  CASE WHEN i = 0 THEN 't' ELSE 'f' END AS eq
FROM (SELECT NULL AS i) AS A

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-10-12 : 16:08:07

hmmm... very strange.

maybe some sort of implicit conversion of the literal (NULL) in the first example when comparing to the number 0, which is different than the the conversion when pulling a value from a SELECT?

just grasping at straws ... but very troubling, indeed.

- Jeff

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-10-12 : 16:58:33

also -- I had almost forgotten this, but ANSI NULLS does not appear to affect JOIN conditions or WHERE clauses in which two columns are compared:

create table #t1 (a int)
insert into #t1
select 1 union
select 2 union
select Null

create table #t2 (b int)
insert into #t2
select 1 union
select 2 union
select Null

set ansi_nulls off

select #t1.a, #t2.b
from #t1
inner join #t2
on #t1.a = #t2.b

select #t1.a, #t2.b
from #t1,#t2
where #t1.a = #t2.b

set ansi_nulls on

go
drop table #t1
drop table #t2

The above returns only the first two rows, not the NULLS that seem like they should match up and JOIN successfully.

- Jeff

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources