Where条款中的通配符函数在哪里？(Within Where Clause criteria like wildcarded function?)

Where条款中的通配符函数在哪里？(Within Where Clause criteria like wildcarded function?)

大家下午好，

所以这是我在论坛上的第二个问题，我相信随着我对TSQL及其动态功能的了解越来越多，这个数字将会增加。 如果任何人都可以建议，如果我正在使用最佳实践方法，它也将不胜感激。

这是我写的一个函数，用于确定用户的位置：

dbo.fnGetCOTLState(CURRENT_USER))

在我创建的一个视图中，我有一个where子句，如下所示：

(dbo.OfficersT.ProcessState = dbo.fnGetCOTLState(CURRENT_USER))

由于最近的变化，我需要更新这个更加动态，所以我更新=（等于），如下所示：

(dbo.OfficersT.ProcessState LIKE dbo.fnGetCOTLState(CURRENT_USER))

但是，我意识到，这仍然不足以提取我需要的记录，我必须通配符dbo.fnGetCOTLState函数，并且来自Microsoft访问后台。我尝试了以下语法：

(dbo.OfficersT.ProcessState LIKE '%' & dbo.fnGetCOTLState(CURRENT_USER)) & '%' (dbo.OfficersT.ProcessState LIKE %dbo.fnGetCOTLState(CURRENT_USER))% (dbo.OfficersT.ProcessState LIKE '%dbo.fnGetCOTLState(CURRENT_USER))%'

但他们都没有工作> _ <这很可能是我没有使用正确的话谷歌，但我似乎无法找到任何人尝试在任何地方的功能使用通配符！ 不知道这是否是可能的，但如果任何人都可以提供一些我想要达到的信息，像往常一样，我会永远感激！

SeanY

Good afternoon all,

So this is my second question on the forum and I'm sure the number will increase as I am learning more about TSQL and its dynamic capabilities. If anyone can advice if I am using the best practice approach it would also be greatly appreciated.

This is a function I wrote to determine where the user is located:

dbo.fnGetCOTLState(CURRENT_USER))

In one of the view I created, I have a where clause as follow:

(dbo.OfficersT.ProcessState = dbo.fnGetCOTLState(CURRENT_USER))

Due to recent changes I need to update this to a to be more dynamic so I updated the = (equal) to a like as follow:

(dbo.OfficersT.ProcessState LIKE dbo.fnGetCOTLState(CURRENT_USER))

However I realised that this is still not sufficient to extract the records I need, I must wild card the dbo.fnGetCOTLState function and coming from Microsoft access background I have tried the following syntax:

(dbo.OfficersT.ProcessState LIKE '%' & dbo.fnGetCOTLState(CURRENT_USER)) & '%' (dbo.OfficersT.ProcessState LIKE %dbo.fnGetCOTLState(CURRENT_USER))% (dbo.OfficersT.ProcessState LIKE '%dbo.fnGetCOTLState(CURRENT_USER))%'

but none of them worked >_< It is highly probable that I am not using the correct words to google, but I can't seem to find anyone trying to use a wild card on a function anywhere! Not sure if it is even possible, but if anyone can offer some info for what I am trying to achieve, as usual I would be eternally grateful!!

SeanY

最满意答案

你的一个尝试很接近。 改变这个：

(dbo.OfficersT.ProcessState LIKE '%' & dbo.fnGetCOTLState(CURRENT_USER)) & '%'

对此：

dbo.OfficersT.ProcessState LIKE '%' + dbo.fnGetCOTLState(CURRENT_USER) + '%'

SQL Server中的字符串连接运算符是加号（ + ），而不是ampsersand。 也许混淆来自你有一个VBA背景，其中＆符号实际上用于字符串连接。

One of your attempts was close. Change this:

(dbo.OfficersT.ProcessState LIKE '%' & dbo.fnGetCOTLState(CURRENT_USER)) & '%'

to this:

dbo.OfficersT.ProcessState LIKE '%' + dbo.fnGetCOTLState(CURRENT_USER) + '%'

The string concatenation operator in SQL Server is plus (+), not ampsersand. Perhaps the confusion comes from your having a VBA background, where ampersand in fact is used for string concatenation.