Difference between two columns

Question

I have one varchar and one int column (which is same in both the tables). I am planning to write a SQL code to extract the difference between each row in both varchar and int columns and record the differences in a different table.

---

Please click "Mark as Answer" if the post solves your problem - Thanks

Answer 1

What do you mean by extracting the difference between values and what is your question?

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 2

It is also unclear to me.  A couple of things, you can try the EXCEPT operator between the two queries or you can do an OUTER JOIN looking for the differences; hang on and I will get a couple of examples.

an Except example:

select varchar_Col, integer_Col from table_1
Except
select varchar_Col, integer_Col from table_2

a Full Join example:

select
  a.varchar_col1 as varchar_col1_1,
  b.varchar_col1 as varchar_col1_2,
  a.integer_col2 as integer_col2_1,
  b.integer_col2 as integer_Col2_2
from table_1 a
full join table_2 b
  on a.varchar_col1 <> b.varchar_col1
  or a.varchar_col1 is null and b.varchar_col1 is not null
  or a.varchar_col1 is not null and b.varchar_col1 is null
  or a.integer_col2 <> b.integer_col2
  or a.integer_col2 is null and b.integer_col2 is not null
  or a.integer_col2 is not null and b.integer_col2 is null

Answer 3

Hi Venu !

You may get the desired output using below query;

SELECT    Col1 AS ColVarchar, Col2 AS ColInteger
FROM    YourTable
WHERE    Col1 <> Col2

Please let me know if this helps. Hopefully i have answered you correctly.

Thanks, Hasham Niaz

Answer 4

I recommend adding a convert to the integer field.

CONVERT(VARCHAR(5), [IntegerField], 0) <> [VarcharField]

Answer 5

I am looking for difference between two tables not the difference in one table.

ex:-

Table A

=====

Testing Table A                          21321231

Testing Table B                          121656416 

Testing Table C                          122

Table B

=====

Testing Table A                          21321231

Testing Table B                          12165641

Testing Table D                          122

Testing Table E                          1224

I am expectingt he output something like this

Testing Table B                          121656416  (because integer column on table B is not matching with table A)

Testing Table C                          122 (because Testing Table D is changed and not matching with table A)

Testing Table E                          1224 (because Testing table B is missing in table A)

I want to extract all the differences found in both the columns on table B which is not matching in table A. If a matching is found in both the columns, I don't want it to be present in the result set.

Thanks a lot for your quick help.

Regards,

Venu S.

Answer 6

Try something like below: Also, could you confirm the expected output for second row: I think it would be "Testing Table D, 122" instead of "Testing Table C, 122".

SELECT	TableB.*
FROM		TableB
LEFT JOIN	TableA
ON			TableB.TestingTableColumn	= TableA.TestingTableColumn
AND			TableB.TestingValueColumn	= TableA.TestingValueColumn
WHERE	TableA.TestingValueColumn IS NULL

Thanks!

Answer 7

TRY THIS,

DECLARE @TESTA TABLE (TEST VARCHAR(12), VALUE INT)
INSERT INTO @TESTA (TEST,VALUE)
VALUES('T.T.A', '21321231'),
      ('T.T.B', '121656416' ),
      ('T.T.C', '122')
      
DECLARE @TESTB TABLE (TEST VARCHAR(12), VALUE INT)
INSERT INTO @TESTB (TEST,VALUE)
VALUES('T.T.A', '21321231'),
      ('T.T.B', '12165641' ),
      ('T.T.D', '122'),
      ('T.T.E', '1224' )
      
SELECT B.* 
FROM @TESTB AS B LEFT OUTER JOIN @TESTA AS A ON 
      A.TEST=B.TEST
WHERE A.VALUE<>B.VALUE OR B.TEST NOT IN (SELECT DISTINCT A.TEST FROM @TESTA AS A)

OR TRY THIS,

DECLARE @TESTA TABLE (TEST VARCHAR(12), VALUE INT)
INSERT INTO @TESTA (TEST,VALUE)
VALUES('T.T.A', '21321231'),
      ('T.T.B', '121656416' ),
      ('T.T.C', '122')
      
DECLARE @TESTB TABLE (TEST VARCHAR(12), VALUE INT)
INSERT INTO @TESTB (TEST,VALUE)
VALUES('T.T.A', '21321231'),
      ('T.T.B', '12165641' ),
      ('T.T.D', '122'),
      ('T.T.E', '1224' )
      
SELECT B.* 
FROM @TESTB AS B LEFT OUTER JOIN @TESTA AS A ON 
      A.TEST=B.TEST
WHERE A.VALUE<>B.VALUE 
      UNION ALL
SELECT B.* FROM @TESTB AS B 
WHERE B.TEST NOT IN (SELECT DISTINCT A.TEST FROM @TESTA AS A  )

AS YOU CAN SEE I DID TT.D instead of T.T.C, please confirm that it is just a typo or you want it that way only?

---

ANK HIT - if reply helps, please mark it as ANSWER or helpful post

Answer 8

Thanks Ank & Deepak. I will try with the options.

The output I am expecting is "Testing Table C" only.

I am trying to get all the values in Table A (which is not equalent to values in Table B), I don't mind if there are any extra values in table B which is not equivalent in Table A. The typo is "Testing Table E" should be in Table A instead of table B. I am sorry I confused you. Please find below the updated expected output.

Table A

=====

Testing Table A                          21321231

Testing Table B                          121656416

Testing Table C                          122

Testing Table E                          1224

Table B

=====

Testing Table A                          21321231

Testing Table B                          12165641

Testing Table D                          122

I am expectingt he output something like this

Testing Table B                          121656416  (because integer column on table B is not matching with table A)

Testing Table C                          122 (because Testing Table D is changed and not matching with table A)

Testing Table E                          1224 (because Testing table E is missing in table B)

Thanks in Advance.

Answer 9

Try

select CharColumn, IntColumn from TableA

EXCEPT

select CharColumn, IntColumn FROM TableB

This will select all different rows from the table A which are different or not exist in Table B

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 10

TRY THIS,

DECLARE @TESTA TABLE (TEST VARCHAR(12), VALUE INT)
INSERT INTO @TESTA (TEST,VALUE)
VALUES('T.T.A', '21321231'),
      ('T.T.B', '121656416' ),
      ('T.T.C', '122'),
      ('T.T.E', '1224' )
      
DECLARE @TESTB TABLE (TEST VARCHAR(12), VALUE INT)
INSERT INTO @TESTB (TEST,VALUE)
VALUES('T.T.A', '21321231'),
      ('T.T.B', '12165641' ),
      ('T.T.D', '122')
      
SELECT A.* 
FROM @TESTB AS B LEFT OUTER JOIN @TESTA AS A ON 
      A.TEST=B.TEST
WHERE A.VALUE<>B.VALUE 
UNION ALL
SELECT A.* FROM @TESTA AS A 
WHERE A.TEST NOT IN (SELECT DISTINCT B.TEST FROM @TESTB AS B )

---

ANK HIT - if reply helps, please mark it as ANSWER or helpful post