CODEWITHSUNDEEP
sql-server-2008substring
nfs
nfs

Reputation: 21

String comparison with in the table

I have two columns with similar data (shown below)

Table1 

CHMedicalCorpINC  
UniversalStd  
SummitCorp

Table2 

CHMedicalCorp  
UniversalStdOrlando  
SummitCorpINC

I want to compare the two columns letter by letter; if I am using substring then it displays values which match up 13 letters or those which have perfect match.

So the output gives me only the CHMedicalCorp not the other two columns. And I want the other two columns to be displayed 

SUBSTRING(a.table1, 1, 13) = SUBSTRING(c.table2, 1, 13)

I want it to show me anything that matched up to 13 or less. Is there a way to do that or a function which allows me to do that?

Upvotes: 1

Views: 51

Answers (1)

Arulkumar
Arulkumar

Reputation: 13237

The query below will work if it has any Id in the table.

SELECT T1.*
FROM TableName T1
JOIN TableName T2 ON T2.Id = T1.Id
WHERE T1.Table1 like '%' + T2.Table2 + '%'
   OR T2.Table2 like '%' + T1.Table1 + '%';

If there is no Id in the table, you can use this way:

SELECT T1.*
FROM TableName T1, TableName T2 
WHERE T1.Table1 like '%' + T2.Table2 + '%'
   OR T2.Table2 like '%' + T1.Table1 + '%'

Sample execution with given data:

DECLARE @TestTable TABLE (Id INT, Table1 VARCHAR (100), Table2 VARCHAR (100));
INSERT INTO @TestTable (Id, Table1, Table2) VALUES 
(1, 'CHMedicalCorpINC', 'CHMedicalCorp'),
(2, 'UniversalStd', 'UniversalStdOrlando'),
(3, 'SummitCorp', 'SummitCorpINC'),
(4, 'Hello', 'Welcome');

SELECT T1.*
FROM @TestTable T1
JOIN @TestTable T2 ON T2.Id = T1.Id
WHERE T1.Table1 like '%' + T2.Table2 + '%'
   OR T2.Table2 like '%' + T1.Table1 + '%'

Upvotes: 1

Related Questions