How to find an EXACT match in Query/SQL string?

[tetsuo2030]

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?

[Mariostg]

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

[tetsuo2030]

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?

[Mariostg]

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.

[ADezii]

I have a solution, but not right now. Will return later.

[tetsuo2030]

Thanks Mario and Dez! I'll check in tomorrow

[ADezii]

The idea is to pass the [Paragraph List] and [Specific Paragraph] Fields to a Public Function included in an SQL Statement. Since [Paragraph List] is a Comma-Delimited List, the Function will create an Array from those Elements and check each Value in the Array against the Value in [Specific Paragraph]. If there is an exact Match, the Function returns 'Match', if not it returns NULL. SQL Statement (assuming Table Name of tblParagraph), Sample Data, and Output to follow:

1. SQL Statement:
   

   SELECT tblParagraph.[Paragraph List], tblParagraph.[Specific Paragraph], 
   fCheckForExactMatch([Paragraph List],[Specific Paragraph]) AS [Paragraph Check]
   FROM tblParagraph;

2. Thanks to mshmyob:
   

   Public Function fCheckForExactMatch(strList As String, strParagraph As String) As Variant
   Dim varList As Variant
   Dim intCtr As Integer
   
   fCheckForExactMatch = Null      'Initialize to NULL
   
   varList = Split(strList, ",")
   
   For intCtr = LBound(varList) To UBound(varList)
     If varList(intCtr) = strParagraph Then      'Matchamundo
       fCheckForExactMatch = "Match"
         Exit Function
     End If
   Next
   End Function

3. Sample Data:
   

   Paragraph List	        Specific Paragraph
   1.0,1.1,1.12,3.2.2.1	         1.1
   1.0,1.1,1.12,3.2.2.1	         1.12
   1.0,1.1,1.12,3.2.2.1	         3.2

4. OUTPUT:
   

   Paragraph List	       Specific Paragraph	Paragraph Check
   1.0,1.1,1.12,3.2.2.1	        1.1	             Match
   1.0,1.1,1.12,3.2.2.1	        1.12	            Match
   1.0,1.1,1.12,3.2.2.1	        3.2	             NULL

[mshmyob]

Hmmmmm, my eyesight must be going... can't see the function.

cheers,

[ADezii]

@mshmyob, it was there all the time! (LOL).

[mshmyob]

LOL - that's cheating.

cheers

[OldBirdman]

Going back to a simple SQL thought, as the OP tried, I think this should work:

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

[ADezii]

Simpler is definitely better, Old Birdman. The only possible advantage that I can see in the Function approach is that it, with slight modification, can return the Index within the Delimited String as opposed to a Starting Position. This, however, is clearly not worth the overhead.

Paragraph List	    Specific Paragraph	    Paragraph Check_2	    Paragraph Check
1.0,1.1,1.12,3.2.2.1	    1.1	             Match at Index: 2	        5
1.0,1.1,1.12,3.2.2.1	    1.12	            Match at Index: 3	        9
1.0,1.1,1.12,3.2.2.1	    3.2		         Null                         0

[OldBirdman]

A function is definitely more versitile. Further, my suggestion relies on there being no blanks before or after the delimiter (comma) in either [Paragraph List] or [Specific Paragraph]. Were that the case, [Paragraph List] would become

Replace([Paragraph List], " ", "")

and the same for [Specific Paragraph]. Beyond that, it could get very messy if a lot of error-checking is required, say for a comma in the [Specific Paragraph] entry by the user. A user function can be expanded easier if further ideas much be implimented in the future.
I joined this thread as often an OP can better understand a solution which simply modifies their original idea, and does not introduce new ideas. Further, my experience with VBA Functions in queries is that they are very slow. Access probably does not optimize the code as well as it does with 'Built-In' functions. But I don't know this for sure, but it seems true with my projects.
However, VBA is definitely easier to debug than SQL or macros, so there is a definite advantage to always using VBA when possible.

[tetsuo2030]

Well all,

I forgot to mention that THERE ARE spaces after the commas in [Paragraph List]; so I used a combo of OB's things:

Paragraph Check: InStr("," & (Replace([Paragraph List]," ","")) & ",","," & [Specific Paragraph] & ",")

This did the trick; however, I really like Dezii's Public Function. Dezii, I got your code to run (though it didn't give the desired results because of my 'spaces' omission). I'm really shaky on the VBA/Function thing, but I'd like to get the function to work in case I have to call it again later in the project. Is that possible?

[Mariostg]

About the space issues, there is a function called trim.
So if you have x=" blabla ", trim(x) will return "blabla". Modify line 10 of Adezii's function.
And if that does not work and you want to get wild, start reading about regular expressions.