Compare 2 column in a same SQL table

Question

I want to compare values from 2 distinct column in a SQL table.

A ---------------- B
10 -------------- 88
20 -------------- 401
30 -------------- 20
40 -------------- 99

I want to keep the values ​​of the second column that are equal to the values ​​of the first column (as 20), but also those that contain a value of the first column (as 401 which contains 40).

So in this example I would see a column C with :

A ---------------- B ----------------- C
10 -------------- 88 ---------------- No
20 -------------- 401 -------------- Yes
30 -------------- 20 ---------------- Yes
40 -------------- 99 ---------------- No

in fact, I can compare fields in the same row but not apply this comparison to all fields in a column.

Is it possible to do that?

Thanks

Answer 1

You can do this with a nested select and a case statement. The nested select uses like to see if there are any rows that meet the similarity condition. Note that the like will find a match if the values are identical as well as when the B column starts with the A column:

select t.*,
       (case when exists (select 1 from table t2 where t1.B like concat(t2.A, '%'))
             then 'Yes'
             else 'No'
        end) as C
from table t;

EDIT:

If by contain, you want '1401' to also match '40', then the pattern for the like should be: concat('%', t2.A, '%').