如何在Query / SQL字符串中找到精确匹配？ [英] How to find an EXACT match in Query/SQL string?

问题描述

大家好，


我在查询中有两个字段：[段落列表]和[特定段落]。 [段落列表]是逗号分隔的段落编号列表（如1.0,1.1,1.1.2等）; [具体段落]仅为1.0。或1.1.2或等等。我想创建一个名为[Paragraph Check]的第三个字段，它将扫描[Paragraph List]以找到它与[Specific Paragraph]相等的位置。我试过这个：

段落检查：InStr（[段落列表]，[特定段落]）


这很有用，因为如果[特定段落]没有出现在[段落列表]中，它给了我一个值0;否则，它给了我[特定段落]（6或20或29）的位置，这没关系，因为如果它是> 0则检查有效。


但是，如果[段落列表]包含3.2.2.1，[特定段落]的值为3.2，我得到一个结果<> 0，因为从技术上讲，3.2在3.2.2.1中找到（这是不好的）。


是否有一个SQL来使它完全匹配？

Hi all,

I have two fields in a query: [Paragraph List] and [Specific Paragraph]. [Paragraph List] is a list of comma-separated paragraph numbers (like 1.0, 1.1, 1.1.2, etc); [Specific Paragraph] is just "1.0" or "1.1.2" or the like. I wanted to create a third field called [Paragraph Check] that would scan through [Paragraph List] to find where it equals [Specific Paragraph]. I tried this:

Paragraph Check: InStr([Paragraph List],[Specific Paragraph])

This was useful because if [Specific Paragraph] didn''t show up in [Paragraph List] it gave me gave the value "0"; otherwise, it gave me the position of the [Specific Paragraph] (6 or 20 or 29), which didn''t matter because if it''s >0 the check works.

HOWEVER, if [Paragraph List] contains 3.2.2.1, and [Specific Paragraph]''s value is 3.2, I get a result <>0 because, technically, 3.2 is found within 3.2.2.1 (which is bad).

Is there a SQL to get this to be an exact match?

推荐答案

可能包括昏迷作为一部分查询： InStr（[field1]，" 1.1.2"&"，"）

Maybe include the coma as part of the query:InStr([field1],"1.1.2" & ",")

更好，更接近，更温暖。我试过了：


InStr（[段落列表]，[特定段落]&"，"）然后它找到所有带逗号的（因为它应该）;但是，每个列表中的最后一个段落后面都没有逗号，所以函数设置它等于0，即列表中找不到。


也许我需要一个IIf语句在这里某个地方？

Better, closer, warmer. I tried:

InStr([Paragraph Listing],[Specific Paragraph] & ",") and it found all the ones with commas after (as it should); however, the last paragraph in each list doesn''t have a comma after it, so the function sets it equal to 0 i.e. not found in list.

Maybe I need an IIf statement in here somewhere?

好的，我们真的伸展它......
InStr（[段落清单]，[具体段落]&" ;，）或权利（[段落清单]，Len [具体段落]））= [具体段落]


我没有测试它，但想法也是为了检查最右边的字符，只要你的长度具体段落......


可能有更优雅的方式。

Ok, we are really stretching it....
InStr([Paragraph Listing],[Specific Paragraph] & ",") OR Right([Paragraph Listing],Len[Specific Paragraph]))=[Specific Paragraph]

I did not test it, but the idea is to also check for the rightmost characters for as long as the length of you Specific paragraph...

There probably is a more elegant way.

这篇关于如何在Query / SQL字符串中找到精确匹配？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！