Comparing data between 2 columns in the same table

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

General SQL Server Forums

New to SQL Server Programming

Comparing data between 2 columns in the same table

Author

Topic

cmstforr
Starting Member

2 Posts

Posted - 2009-08-11 : 09:29:16

Hi there,

I've written a very simple query that compares the values between 2 columns. The aim of this is to show me any records where column 1 (Name) does not equal column 2 (GA_Name).

For Example:
select * from dbo.Cus_Sup
where
[Name] <> GA_Name

This does not seem to work for records where the second column has null values. For example:

ACTUAL RESULT
*************

Name Global Address Name
Tom Toms
Frank NULL
Sue Sue
Bob Robert

Existing Output
Tom Toms
Bob Robert

I require the record equal to Frank to be returned in my output as well.

Apprecite anyone's assistance on this.

Many thanks,
Tom

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2009-08-11 : 09:39:53

Try this...

select * from dbo.Cus_Sup
where COALESCE([Name],'') <> COALESCE(GA_Name,'')

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-08-11 : 10:33:04

which will not work when comparing NULL vs the empty string (unless you want to treat them the same way)

if you need to distinguish nulls from empty strings then I think you are stuck with this


SELECT
    *
FROM
    dbo.[Cus_Sup]
WHERE
    (
        [name] <> [GA_Name]
        OR ([name] IS NULL AND [GA_Name] IS NOT NULL)
        OR ([name] IS NOT NULL AND [GA_Name] IS NULL)
    )

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

cmstforr
Starting Member

2 Posts

Posted - 2009-08-12 : 04:29:02

Excellent thanks for both of your help.

Tom

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources