CODEWITHSUNDEEP
mysql
Rio
Rio

Reputation: 14882

How to find if two rows are similar to each other?

Is there a way to see if two rows are similar to each other in SQL, and how many of such exist? 

select columna,columnb from table1 
having columna like columnb

Something like that? A matching of '%columnb%' would work perfectly, because I know that columnb is always a "subset" of columna.

| columna        | columnb      |
---------------------------------
| www.reddit.com | reddit       | 
| www.cnn.com    | bbc          |
| www.yahoo.com  | yahoo        |

Ideally I'd like a result like this.

| count(*)       | state        |
---------------------------------
| 2              | similar      | 
| 0              | notsimilar   |

Thanks in advance.

Upvotes: 0

Views: 34

Answers (2)

smoore4
smoore4

Reputation: 4866

You can do this with LIKE. It looks like this:

SELECT State, COUNT(State)
FROM
(
SELECT varcol1, varcol2,
CASE WHEN varcol1 LIKE CONCAT('%',varcol2,'%') THEN 'similar'
ELSE 'not similar' END AS State
FROM test.test) a
GROUP BY State;

Upvotes: 1

Mudassir Hasan
Mudassir Hasan

Reputation: 28741

SELECT Count(*),state
FROM
(
SELECT CASE WHEN LOCATE(columnB,ColumnA)<>0 THEN 'notsimilar' 
                 ELSE 'similar' END as state
FROM tableName
) as Z
GROUP BY state

You can use LOCATE() function to check whether columnB value lies in columnA

Upvotes: 0

Related Questions