TSQL Server离开CHARINDEX

Question

我有一个TSQL语句，它查询单个表，查找重复的街道地址号。例如，"123街“与"123街”匹配。我使用CHARINDEX分隔字符串，方法是选择字符串左边的字符，但在空格(几乎总是数字)之前选择，如下所示：

    "SELECT NewId() as NewId," +
            //We rename the dbo.User table as "a" then rename it again as "b" so we can look for duplicate Street Address numbers
            "a.Id AS LeftID,a.DateSubmitted AS LeftDateSubmitted,a.Updated AS LeftUpdated," +
            "a.Status AS LeftStatus,a.StreetAddress AS LeftStreetAddress," +

            "b.Id AS RightID,b.DateSubmitted AS RightDateSubmitted,b.Updated AS RightUpdated," +
            "b.Status AS RightStatus,b.StreetAddress AS RightStreetAddress " +

            //We join the 2 virtual dbo.User tables where table b Id's are greater than table a meaning b records are newer
            "FROM [User] a JOIN [User] b ON b.Id > a.Id AND " +

            //LEFT selects the left most characters (usually numbers) in the StreetAddress field string before the space ' '
            //and eliminates the rest of the address isolating just the street address numbers for matching
            "LEFT(a.StreetAddress,CHARINDEX(' ',a.StreetAddress)) = LEFT(b.StreetAddress,CHARINDEX(' ',b.StreetAddress)) " +

            //Don't show orange or blue status records
            "AND b.Status != 'Orange' AND a.Status != 'Orange' AND a.Status != 'Blue' AND b.Status != 'Blue' " +

            //If a b record (newer) is red then ignore because it is completed and ignore a records (oldest) older than 90 days
            "WHERE a.DateSubmitted >= (GetDate() - 90) AND b.Status != 'Red' " +

            //Show newest records first
            "ORDER BY b.DateSubmitted DESC"

直到今天，这一做法一直运作得相当顺利。人们注意到，如果该人输入地址的所有大写内容如下所示，就会发现出现假阳性：

​

我对以下用途的理解：

    LEFT(a.StreetAddress,CHARINDEX(' ',a.StreetAddress))

会不会在用于联接的空格之前产生最左边的字符，但是上面的图像显示了不应该发生的匹配？请帮助SQL初学者..。

Answer 1

我不认为您要发送到数据库服务器以获取这些结果的查询正是您使用该代码生成的查询。不仅是地址匹配不当..。您还可以看到，ID在b中比a少。设置将查询发送到服务器的断点，检查查询字符串并验证实际发送的内容。