SQL Server 2008 R2 Inner Join Fails to match varch

问题:

We are using Microsoft SQL Server 2008 R2 for one of our Classic ASP applications.

We have two tables:

• TableA
• TableB

TableA has the following columns:

• InstName(varchar[1024])

TableB has these columns:

• InstitutionName(varchar[1024])

However, we came across some data like the following

TableA

InstName with value University of Nantes [ Universites de Nantes ]

TableB

InstitutionName with value University of Nantes [ Universites de Nantes ]

We need to run inner join select queries on the said 2 tables:

select *
from TableA
inner join TableB on TableA.InstName LIKE TableB.InstitutionName

Sadly, this inner join fails to match the columns with the value of University of Nantes [ Universites de Nantes ]

I do know that "[]" square brackets have special meaning in Microsoft SQL Server 2008 R2.

Please modify the said inner join select query in such a way that it escapes the "[]" square brackets so that the match is found, and paste the said modified query on this stackoverflow.com ticket.

Furthermore, please advise on how I should deal with other special characters like "[]" square brackets that have special meaning in Microsoft SQL Server 2008 R2.

回答1:

If you need to JOIN on two fields using LIKE that contain [, you'll need to escape the opening bracket since the bracket is a wildcard character. For any wildcard characters, you can use brackets [] - this will search for any single character within the specified range.

For example, this should work in your scenario using REPLACE:

select * 
from yourtable t inner join 
    yourtable2 t2 on t.field like replace(t2.field,'[', '[[]')

• SQL Fiddle Demo

Alternatively, for your sample data, you have no reason to use LIKE as = would work the same.

回答2:

Use = instead of LIKE.

Your query should read:

select *
     from TableA
     inner join TableB
     on TableA.InstName = TableB.InstitutionName

I confirmed with the following:

declare @t1 table(name varchar(1024))
insert into @t1(name) values('[ Universites de Nantes ]');

declare @t2 table(name varchar(1024))
insert into @t2(name) values('[ Universites de Nantes ]');

--nothing returned
    select * from @t1 t1
    inner join @t2 t2 ON t2.name LIKE t1.name

--one record returned
    select * from @t1 t1
    inner join @t2 t2 ON t2.name = t1.name