Compare two columns with different data types

Question

I have two columns in a table with different data types, how can i compare these two columns to look for similar values.

Table
column_a(int)  column_b(nvarchar)
-------------  -----------------
1               Apple
2               Mango
3               5
4               2
5               Book

I tried below query but did not work. Kindly suggest.

SELECT * FROM tab
WHERE
CAST(column_a AS nvarchar)
IN 
(SELECT column_b FROM tab)

Thanks in advance

Can you give us an example of similar value between the two columns ? — Amira Bedhiafi, Dec 12 '19 at 11:27
The real question is why are you storing what is supposed to be numerical data in a `varchar`? Alphanumerical and Numerical data types don't act the same. For example, `'10'` is **less than** `'2'`. — Thom A, Dec 12 '19 at 11:30
@Larnu , nvarchar column is for comments, thus it has got num values as well — Falinn, Dec 12 '19 at 11:38
@xXx , Thanks, i want to view row 2 and 5 to be printed as output — Falinn, Dec 12 '19 at 11:47
@Larnu , i mean this column is for comments , 2 is a form of comment in my table — Falinn, Dec 12 '19 at 11:56
It appears that the OPs sample data is [not representative](https://stackoverflow.com/questions/59303470/compare-two-columns-with-different-data-types/59304378#comment104811507_59304378) of their real data; making the real problem impossible to debug. — Thom A, Dec 12 '19 at 12:47

Akash Shrivastava · Answer 1 · 2019-12-12T11:42:03.400

0

Use self join for this

SELECT A.* 
FROM TAB A
INNER JOIN TAB B ON CAST(A.column_a as nvarchar) = LTRIM(RTRIM(B.column_b))

edited Dec 12 '19 at 11:42

answered Dec 12 '19 at 11:29

Akash Shrivastava

1,365
8
16

Hey Akash, Query runs but Gives zero records. – Falinn Dec 12 '19 at 11:35
That's weird, I tested on my own DB and it works, maybe you have whitespaces in `NVARCHAR` column, try trimming the values (see updated query in snippet above) – Akash Shrivastava Dec 12 '19 at 11:42
Unfortunately, Zero records even after trim UPDATE tab SET [column] = LTRIM(RTRIM([column])) – Falinn Dec 12 '19 at 11:54
it worked fine on a test table, but did not worked with my existing table, there might be an issue with my current table. Thanks – Falinn Dec 12 '19 at 12:30
Oh, yeah, add more details when you find them and need help. [what to do when someone answers your queston](https://stackoverflow.com/help/someone-answers) – Akash Shrivastava Dec 12 '19 at 14:19

score 0 · Answer 2 · answered Dec 12 '19 at 12:15

0

I would use TRY_CONVERT and an EXISTS to do this:

CREATE TABLE dbo.YourTable (ColumnA int,
                            ColumnB nvarchar(5));
INSERT INTO dbo.YourTable (ColumnA,ColumnB)
VALUES (1,'Apple'),
       (2,'Mango'),
       (3,'5'),
       (4,'2'),
       (5,'Book');
GO

SELECT YT.ColumnA,
       YT.ColumnB
FROM dbo.YourTable YT
WHERE EXISTS (SELECT 1
              FROM dbo.YourTable sq
              WHERE YT.ColumnA = TRY_CONVERT(int,sq.ColumnB));

DB<>Fiddle

This, however, will perform poorly in a large table as the query is not SARGable (due to the TRY_CONVERT). As I mentioned in the comments, if you need to store numerical data, use a numerical data type.

answered Dec 12 '19 at 12:15

Thom A

88,727
11
45
75

it worked fine on a test table, but did not worked with my existing table, there might be an issue with my current table. Thanks – Falinn Dec 12 '19 at 12:25
Then provide us with representative sample data, @FaisalAlinn . It's no good giving us sample data that doesn't replicate the problem and then telling us the SQL we provide "doesn't work", when it does for the sample(s) you've provided. – Thom A Dec 12 '19 at 12:26
Thanks for your concern and support. I am afraid as the data is categorized as confidential by the company. – Falinn Dec 12 '19 at 12:45
I didn't ask for *actual* data, @FaisalAlinn, I asked for **representative**. Not the same thing. – Thom A Dec 12 '19 at 12:45

Compare two columns with different data types

2 Answers2