CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2014
Suzy
Suzy

Reputation: 103

Comparing non identical fields in two different tables

I am trying to compare names in 2 different tables.

In Table1 the field is called Name1 and has values like Lynn Smith.

In Table2, the field is called Name2 and it has the value like Lynn Smith (Extra)

How can I compare the two name values ignoring the text in the brackets? I want to write a query where I need some other fields where the main name is the same.

Upvotes: 3

Views: 98

Answers (3)

russ
russ

Reputation: 624

If all the values in Table2 Column2 have space between the end of the second name and the first (open) bracket then you could use this:

SELECT SUBSTRING('Lynn Smith (Extra)',1,PATINDEX('%(%','Lynn Smith (Extra)')-2)

If you were to replace 'Lynn Smith (Extra)' with the column name:

SELECT SUBSTRING('name2',1,PATINDEX('%(%','name2')-2)

then it would show a list of the values in name2 without the text in the brackets, in other words, in the same format (as such) as the names in name1 on table1.

SUBSTRING and PATINDEX are String functions.

SUBSTRING asks for three 'arguments': (1) expression (2) start and (3) length.

(1) As you can see above the first argument can be (amongst other things) either a constant - 'Lynn Smith (Extra)' or a column - 'name2'

(2) the start of the result you want so, in this example, the first (or left) character in the string in the column or constant is signified by the number 1.

(3) how many characters do you want to see in the result? In this example I have used PATINDEX to create a number (see below).

PATINDEX asks for two arguments: (1) %pattern% and (2) expression

(1) is the character or group of characters (shape or 'pattern') you are looking to locate, the reason for the wildcard characters %% either side of the pattern is because there may be characters either side of the pattern

(2) is (amongst other things) the constant or column that contains the pattern from argument 1.

Whilst SUBSTRING returns character data (part of the string) PATINDEX produces a number, that number is the first character in the pattern (given as a number, counting from the left of the expression).

Upvotes: 0

DhruvJoshi
DhruvJoshi

Reputation: 17126

One way to do this is to direct compare the names after cleaning up on one side. Unlike Gordon's answer, I'd do this with another table containing data to compare from table2.

SELECT Table2Id, Name2, NULL as cleanedName INTO NewTable FROM Table2

Now we update the cleanedName column to strip off extra information from Name2 column like below. You may also create an index on this table.

UPDATE cleanedName
SET cleanedName = LEFT (name2,CHARINDEX('(',Name2))

Now drop and re-create index on CleanedName column and then compare with Table1.Name1 column

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269753

One method would use like:

select . . . 
from t1 join
     t2
     on t2.name2 like t1.name1 + ' (%)';

However, this is probably not efficient. If you want performance, you can extract the name into a separate column in the second table and create an index on it:

alter table t2 add column name_cleaned as
    (left(name2, charindex(' (', name2 + ' (') - 2));

create index idx_t2_name_cleaned on t2(name_cleaned);

Then you can phrase the query as:

select . . . 
from t1 join
     t2
     on t2.name2_cleaned = t1.name1;

Upvotes: 3

Related Questions