如何在 JOIN 的 WHERE 子句中使用列名作为 LIKE 语句的一部分 [英] How to use column name as part of a LIKE statement in a WHERE clause of a JOIN

问题描述

LEFT OUTER JOIN [INVENTTRANS]
ON #TEMP.VOUCHERPHYSICAL=[INVENTTRANS].VOUCHERPHYSICAL
WHERE [INVENTTRANS].ITEMID = #Temp.INVENTDIMID

这几乎为我提供了我正在寻找的结果.由于 INVENTDIMID 在某些情况下附加了后缀，因此大约 1/4 的预期结果被消除了.

This provides me nearly the result I am looking for. About 1/4th of the desired results are eliminated because INVENTDIMID has a suffix appended to it in certain cases.

在类似于此意图的类似子句中成功的正确语法是什么.

What is the proper syntax to succeed in a like clause similar to this intent.

WHERE '[INVENTTRANS].ITEMID%' like #Temp.INVENTDIMID

或者，如果没有快捷方式可以做到这一点，那么最有效的长线方式是什么.

Alternatively, if there is no short hand way to do this, what is the most effective long handed way about it.

谢谢

推荐答案

我不确定我是否完全理解这个问题，但您似乎想将谓词应用于比较固定而不是固定的其他内容的 like 语句您提供的文字.

I am not sure if I completely understand the question but it appears you want to apply a predicate to a like statement that compares something else that is fixed and not in text you supply.

我总是这样做:

Where (ColumnName) like '%' + (OtherColumnName) + '%'

这基本上是在您尝试关联的列的任一侧应用通配符.它适用于变量或列.

This is basically applying a wildcard around either side of the column you are trying to relate to. It will work for variables or columns.

更新 5-21-13

两个表变量的简单示例，它们自填充并在第二个表变量上使用通配符匹配.代码将在 SQL Management Studio 中的 SQL 2008 或更高版本上按原样运行:

Simple example of two table variables that self populate and work with wild card matcing on the second table variable. Code will run as is on SQL 2008 or higher in SQL Management Studio:

declare @Person Table ( personID int identity, person varchar(8));

insert into @Person values ('Brett'),('Sean'),('Chad'),('Michael'),('Ray'),('Erik'),('Queyn');

declare @Match table ( Description varchar(4));

insert into @Match values ('B'), ('S'),('e')

Select top 100
    p.*
,   m.Description as 'MatchedOn'
from @Person p, @Match m
where p.person like '%' + m.Description + '%'

这篇关于如何在 JOIN 的 WHERE 子句中使用列名作为 LIKE 语句的一部分的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！