Determine if two fields are different

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

Transact-SQL (2000)

Determine if two fields are different

Author

Topic

X-Factor
Constraint Violating Yak Guru

392 Posts

Posted - 2006-01-17 : 22:09:28

Hi,

Is there a better way of doing this than this...


a <> b
OR
a IS NULL AND b IS NOT NULL
OR
a IS NOT NULL AND b IS NULL

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2006-01-17 : 23:54:24

Who knows???

It sort of depends on what you are trying to accomplish.


DECLARE 
	@a CHAR(2),
	@b CHAR(2)

SELECT @a = 1, @b = 2

SELECT CASE WHEN ISNULL(@a,@b+1)<> ISNULL(@b,@a+1) THEN 'TRUE' ELSE 'FALSE' END

SELECT @a = 2, @b = 2

SELECT CASE WHEN ISNULL(@a,@b+1)<> ISNULL(@b,@a+1) THEN 'TRUE' ELSE 'FALSE' END

SELECT @a = NULL, @b = 2

SELECT CASE WHEN ISNULL(@a,@b+1)<> ISNULL(@b,@a+1) THEN 'TRUE' ELSE 'FALSE' END

SELECT @a = 2, @b = NULL

SELECT CASE WHEN ISNULL(@a,@b+1)<> ISNULL(@b,@a+1) THEN 'TRUE' ELSE 'FALSE' END

SELECT @a = NULL, @b = NULL

SELECT CASE WHEN ISNULL(@a,@b+1)<> ISNULL(@b,@a+1) THEN 'TRUE' ELSE 'FALSE' END

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-01-18 : 01:20:17

declare @t table(a int, b int)
insert into @t select 12,8 union all
select 2,Null union all
select Null,8 union all
select 152,152 union all
select Null,Null
select * from @t where a<>b
select * from @t where a is Null and b is not null
select * from @t where a is not Null and b is null

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2006-01-18 : 01:43:55

"Is there a better way of doing this than this"

I don't think so. Boring isn't it!

SELECT CASE WHEN ISNULL(@a,@b+1)<> ISNULL(@b,@a+1) THEN 'TRUE' ELSE 'FALSE' END

will fail when @a or @b is the max value for the datatype [long odds I grant you]

I have some code that generates a "pessimistic where clause" for all columns in a table - taking into account datatype etc. (can't do Equals / NotEquals on n/TEXT, so does a comparison on CAST to VARCHAR(8000) only + length comparison [which is far from foolproof of course])

Anyways, it saves typing the conditions out long hand.

You could use SET ANSI_NULLS OFF but personally I wouldn't.

Kristen

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-01-18 : 02:31:36

>>Is there a better way of doing this than this...

The better(or simple

) way may be

Select columns from yourTable where Isnull(a,'')<>Isnull(b,'')

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2006-01-18 : 04:23:27

"Isnull(a,'')<>Isnull(b,'')"

If A='' and B IS NULL then they will appear to be equal. Maybe that's acceptable - but that's the problem, you always have to come up with a "value" to represent NULL, and of course the column CAN contain that same value, so you leave yourself open to an accidental match, when inn fact there isn't one.

IMHO: Either you allow NULLs in the column and do the full "verbose" comparison, or you don't allow NULLs and have a default value of empty-string '', or Zero for numerics, or something similar.

Personally I do prefer to adopt NULL as "the value of this column is unknown" and program in the expectation that I will have a mix of NULL and non-NULL values in that column (of course I do have NOT NULL columns too where the value is obviously "compulsory"

)

Kristen

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2006-01-18 : 08:52:56

quote:
Originally posted by Kristen

"Is there a better way of doing this than this"

I don't think so. Boring isn't it!

SELECT CASE WHEN ISNULL(@a,@b+1)<> ISNULL(@b,@a+1) THEN 'TRUE' ELSE 'FALSE' END

will fail when @a or @b is the max value for the datatype [long odds I grant you]

I have some code that generates a "pessimistic where clause" for all columns in a table - taking into account datatype etc. (can't do Equals / NotEquals on n/TEXT, so does a comparison on CAST to VARCHAR(8000) only + length comparison [which is far from foolproof of course])

Anyways, it saves typing the conditions out long hand.

You could use SET ANSI_NULLS OFF but personally I wouldn't.

Kristen

I'll take your odds Kristen. They're astronomical given large VARCHAR or INT values. You could make a "take everything into account" solution, but why?

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

Kristen
Test

22859 Posts

Posted - 2006-01-19 : 03:08:14

"'ll take your odds Kristen"

I thought you might!

However, your solution, for INTs, is pretty nifty.

I'm less keen on

WHERE COALESCE(@MyVarcharA, '') <> COALESCE(@MyVarcharB, '')

I mean - wouldn't you define them as NOT NULL and just store blank strings if that's how you want to represent them?

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources