How to Compare Two Columns for Equality in SQL Server

How do I compare two columns for equality in SQL Server?

What's wrong with CASE for this? In order to see the result, you'll need at least a byte, and that's what you get with a single character.

CASE WHEN COLUMN1 = COLUMN2 THEN '1' ELSE '0' END AS MyDesiredResult

should work fine, and for all intents and purposes accomplishes the same thing as using a bit field.

How do I compare two columns in SQL Server 2016 and if not equal, set them equal based off a condition?

SQL Variables start with an at "@" sign. These look like columns. If you are wanting to set the columns then...

UPDATE myTable
SET S_End = P_EndDate
WHERE S_End > P_EndDate

If you don't want to change the data, you just want your query to show P_EndDate in those cases, then use a case statement:

SELECT status, ID, SStart, S_End = CASE
   WHEN S_End > P_EndDate THEN P_EndDate
   ELSE S_End
   END, P_EndDate, comment FROM myTable
WHERE SStart IS NOT NULL AND STATUS = 'NOT DONE' AND S_End <> P_EndDate

If you really want variables, then you need to declare them and set their values.

How to compare two columns in SQL server

Try this:

SELECT CASE WHEN REPLACE (Table1.ColName1,'.','') = Table2.ColName2 
            THEN 'Equal' 
            ELSE 'Not Equal' 
            END AS IsEqual
    FROM Table1
    INNER JOIN Table2 ON Table1.PrimaryKey = Table2.ForeignKey

This query will return Equal if they are equal and Not Equal if they are not.

REPLACE() will remove . from ColName1.

sql server compare two column from different table

You could check if records exist on one side or the other only by joining the two tables left-to-right and right-to-left. If there are any records not matched, it's false, otherwise true.

select case when exists (
    select top 1 1
    from Table1 t1
    left join Table2 t2 on t2.item = t1.item
    where t2.item is null
    
    union all

    select top 1 1
    from Table2 t2
    left join Table1 t1 on t1.item = t2.item
    where t1.item is null
) 
    THEN 'false'
    ELSE  'true'
END as result

How to Compare Two Columns and Display the difference in a new Column - SQL Server

The solution below works one way: col1 words are removed from col2.

Sample data

create table test
(
  id int,
  col1 nvarchar(max),
  col2 nvarchar(max)
);

insert into test (id, col1, col2) values
(1, 'This is my test case', 'This is not my test Case, leave me alone');

Solution

with cte as
(
  select t.id,
         replace(s.value, ',', '') as word
  from test t
  cross apply string_split(t.col2, ' ') s
    except
  select t.id,
         replace(s.value, ',', '')
  from test t
  cross apply string_split(t.col1, ' ') s
)
select string_agg(c.word, ' ') as result
from cte c
group by c.id;

Result

result
------------------
alone leave me not

Fiddle to see things in action with intermediate results.

---

New solution

Perhaps this version does not look so clean, but it should preserve the word order...

with cte as
(
  select t.id,
         row_number() over(order by (select null)) as sort,
         replace(s.value, ',', '') as word
  from test t
  cross apply string_split(t.col2, ' ') s
  where not exists ( select 'x'
                     from test t
                     cross apply string_split(t.col1, ' ') s2
                     where replace(s2.value, ',', '') = replace(s.value, ',', '') )
)
select string_agg(c.word, ' ') within group (order by c.sort) as result
from cte c
group by c.id;

New result

result
------------------
not leave me alone

New fiddle.

compare two columns and select only one with sql query

One of the two is always zero?

SELECT col1+col2

How do I compare two columns from different tables in different databases?

You can access any database object from any database context by fully qualifying the object name in the form of database.schema.object.

Using SQL Server you are better off using the sys schema, which (if performance matters) is better than using the information_schema schema.

So you can do

select name
from database_1.sys.columns
where object_id=object_id(N'database_1.sys.table_1')
except
select name
from database_2.sys.columns
where object_id=object_id(N'database_2.sys.table_2')

Check if two columns are equal for all rows per group

You can use HAVING and CASE:

SELECT [Date]
FROM #tab
GROUP BY [Date]
HAVING SUM(CASE WHEN Attribute1 = Attribute2 THEN 0 ELSE 1 END) = 0

LiveDemo

Otherwise is there a clever way to this using COUNT maybe?

Why not :) Version with COUNT:

SELECT [Date]
FROM #tab
GROUP BY [Date]
HAVING COUNT(CASE WHEN Attribute1 <> Attribute2 THEN 1 END) = 0

LiveDemo2

---

Related Topics