SQL Server 2008 R2内部联接由于包含特殊字符而无法匹配varchar字段 [英] SQL Server 2008 R2 Inner Join Fails to match varchar fields because it contains special characters

问题描述

我们将Microsoft SQL Server 2008 R2用于我们的经典ASP应用程序之一.

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

我们有两个表:

• TableA
• TableB

TableA包含以下列:

• InstName(varchar[1024])

TableB包含以下列:

• InstitutionName(varchar[1024])

但是，我们遇到了一些类似以下的数据

However, we came across some data like the following

表A

InstName具有价值的南特大学[南特大学]

InstName with value University of Nantes [ Universites de Nantes ]

TableB

InstitutionName具有价值的南特大学[南特大学]

InstitutionName with value University of Nantes [ Universites de Nantes ]

我们需要在上述2个表上运行内部联接选择查询:

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 ]

我确实知道在Microsoft SQL Server 2008 R2中，"[]"方括号具有特殊含义.

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

请修改所述内部联接选择查询，使其逃脱"[]"方括号，以便找到匹配项，并将所述修改后的查询粘贴到此stackoverflow.com票证上.

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.

此外，请告知我如何处理Microsoft SQL Server 2008 R2中具有特殊含义的其他特殊字符，例如"[]"方括号.

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.

推荐答案

如果需要使用包含[的LIKE在两个字段上进行JOIN，则由于括号是通配符.对于任何通配符，可以使用方括号[]-这将搜索指定范围内的任何单个字符.

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.

例如，这应该在您使用REPLACE的情况下起作用:

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小提琴演示
• SQL Fiddle Demo

或者，对于您的示例数据，您没有理由使用LIKE，因为=会起到相同的作用.

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

这篇关于SQL Server 2008 R2内部联接由于包含特殊字符而无法匹配varchar字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！