CODEWITHSUNDEEP
sqlsql-servert-sql
lili
lili

Reputation: 81

How can I compare two columns for similarity in SQL Server?

I have one column that called 'message' and includes several data such as fund_no, detail, keywords. This column is in table called 'trackemails'.

I have another table, called 'sendemails' that has a column called 'Fund_no'.

I want to retrieve all data from 'trackemail' table that the column 'message' contains characters same as 'Fund_no' in 'trackemails' Table.

I think If I want to check the equality, I would write this code:

select
case when t.message=ts.fund_no then 1 else 0 end
from trackemails t, sendemails s

But, I do want something like below code:

select
case when t.message LIKE ts.fund_no then 1 else 0 end
from trackemails t, sendemails s

I would be appreciate any advice to how to do this:

Upvotes: 0

Views: 2554

Answers (2)

El.Hum
El.Hum

Reputation: 1485

Dear Check SQL CHARINDEX() Function. This function finds a string in another string and returns int for the position they match. Like 

SELECT CHARINDEX('ha','Elham')  
-- Returns: 3

And as you need: 

 SELECT * 
        ,(SELECT * 
         FROM sendemail 
         WHERE CHARINDEX(trackemails.Message,sendemail.Fund_No)>0 )
 FROM trackemails

For more information, If you want something much better for greater purposes, you can use Fuzzy Lookup Component in SSDT SSIS. This Component gives you a new column in the output which shows the Percentages of similarity of two values in two columns.

Upvotes: 1

MPost
MPost

Reputation: 535

 SELECT *
 FROM trackemails tr
   INNER JOIN sendemail se on tr.Message like '%' + se.Fund_No + '%'

Upvotes: 2

Related Questions