469,336 Members | 5,271 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,336 developers. It's quick & easy.

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

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?
Nov 3 '10 #1
23 13649
Mariostg
332 100+
Maybe include the coma as part of the query:InStr([field1],"1.1.2" & ",")
Nov 3 '10 #2
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?
Nov 3 '10 #3
Mariostg
332 100+
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.
Nov 3 '10 #4
ADezii
8,800 Expert 8TB
I have a solution, but not right now. Will return later.
Nov 3 '10 #5
Thanks Mario and Dez! I'll check in tomorrow
Nov 3 '10 #6
ADezii
8,800 Expert 8TB
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:
    Expand|Select|Wrap|Line Numbers
    1. SELECT tblParagraph.[Paragraph List], tblParagraph.[Specific Paragraph], 
    2. fCheckForExactMatch([Paragraph List],[Specific Paragraph]) AS [Paragraph Check]
    3. FROM tblParagraph;
  2. Thanks to mshmyob:
    Expand|Select|Wrap|Line Numbers
    1. Public Function fCheckForExactMatch(strList As String, strParagraph As String) As Variant
    2. Dim varList As Variant
    3. Dim intCtr As Integer
    4.  
    5. fCheckForExactMatch = Null      'Initialize to NULL
    6.  
    7. varList = Split(strList, ",")
    8.  
    9. For intCtr = LBound(varList) To UBound(varList)
    10.   If varList(intCtr) = strParagraph Then      'Matchamundo
    11.     fCheckForExactMatch = "Match"
    12.       Exit Function
    13.   End If
    14. Next
    15. End Function
  3. Sample Data:
    Expand|Select|Wrap|Line Numbers
    1. Paragraph List            Specific Paragraph
    2. 1.0,1.1,1.12,3.2.2.1             1.1
    3. 1.0,1.1,1.12,3.2.2.1             1.12
    4. 1.0,1.1,1.12,3.2.2.1             3.2
  4. OUTPUT:
    Expand|Select|Wrap|Line Numbers
    1. Paragraph List           Specific Paragraph    Paragraph Check
    2. 1.0,1.1,1.12,3.2.2.1            1.1                 Match
    3. 1.0,1.1,1.12,3.2.2.1            1.12                Match
    4. 1.0,1.1,1.12,3.2.2.1            3.2                 NULL
Nov 3 '10 #7
mshmyob
904 Expert 512MB
Hmmmmm, my eyesight must be going... can't see the function.

cheers,
Nov 3 '10 #8
ADezii
8,800 Expert 8TB
@mshmyob, it was there all the time! (LOL).
Nov 3 '10 #9
mshmyob
904 Expert 512MB
LOL - that's cheating.

cheers
Nov 3 '10 #10
OldBirdman
675 512MB
Going back to a simple SQL thought, as the OP tried, I think this should work:
Expand|Select|Wrap|Line Numbers
  1. Paragraph Check: InStr("," & [Paragraph List] & ",", "," & [Specific Paragraph] & ",")
Nov 4 '10 #11
ADezii
8,800 Expert 8TB
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.
Expand|Select|Wrap|Line Numbers
  1. Paragraph List        Specific Paragraph        Paragraph Check_2        Paragraph Check
  2. 1.0,1.1,1.12,3.2.2.1        1.1                 Match at Index: 2            5
  3. 1.0,1.1,1.12,3.2.2.1        1.12                Match at Index: 3            9
  4. 1.0,1.1,1.12,3.2.2.1        3.2                 Null                         0
Nov 4 '10 #12
OldBirdman
675 512MB
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
Expand|Select|Wrap|Line Numbers
  1. 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.
Nov 4 '10 #13
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?
Nov 5 '10 #14
Mariostg
332 100+
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.
Nov 5 '10 #15
ADezii
8,800 Expert 8TB
The addition of Line #7 to the Function should do the trick.
Expand|Select|Wrap|Line Numbers
  1. Public Function fCheckForExactMatch(strList As String, strParagraph As String) As Variant
  2. Dim varList As Variant
  3. Dim intCtr As Integer
  4.  
  5. fCheckForExactMatch = Null      'Initialize to NULL
  6.  
  7. strList = Replace(strList, " ", "")
  8.  
  9. varList = Split(strList, ",")
  10.  
  11. For intCtr = LBound(varList) To UBound(varList)
  12.   If varList(intCtr) = strParagraph Then      'Matchamundo
  13.     fCheckForExactMatch = "Match"
  14.       Exit Function
  15.   End If
  16. Next
  17. End Function
Nov 5 '10 #16
ADezii
8,800 Expert 8TB
Trim() will not eliminate internal spaces, which I thing is needed in this instance. Replace() will resolve this issue.
Nov 5 '10 #17
Mariostg
332 100+
I know, that is why I suggested to apply the Trim() after the Split(). Hey, but Replace() is fine with me. We replied almost at the same time. I will wait longer next time... If I had seen your reply first, I would have refrain from replying. At least, the OP is learning new functions...
Nov 5 '10 #18
ADezii
8,800 Expert 8TB
Sorry about the confusion. The idea of using Trim() after Split() never actually crossed my mind, afterall, I am only one-dimensional! (LOL).
Nov 5 '10 #19
OldBirdman
675 512MB
Using Trim() after Split() will do the job, but using Replace() also handles blanks within the strings.
If [Paragraph List] is 1.0, 1.1, 1.1.2, etc, and [Specific Paragraph] is 1.1 it doesn't matter, but if [Paragraph List] becomes 1.0, 1.1, 1. 1.2, or [Specific Paragraph] is entered as 1. 1 , then Replace() is better. But that could NEVER happen, so why worry?
Nov 5 '10 #20
Mariostg
332 100+
Well, I would say you have to worry, because when you use Replace(), you actually modify the dataset. I don't know how the [Paragraph List] is populated to build the list, but if after the Trim(split(X)) I have a string that ends with something else than a digit, I would worry that something is wrong somewhere. Either an extra space was typed, or a digit has been missed. In Python, there is an Endswith() function, so nice...
Nov 5 '10 #21
OldBirdman
675 512MB
"...because when you use Replace(), you actually modify the dataset."
I don't understand this statement. I thought that we were creating a calculated field 'Paragraph Check:' which is a number from 0 to ?(255). There should be no alteration of either [Paragraph List] or [Specific Paragraph]. Can you explain your statement?
Nov 5 '10 #22
Mariostg
332 100+
Yes, I guess we are creating [Paragraph Check]. And that has no relationship with what I was saying. But what I was trying to say is that I don't know how [Paragraph List] is initially created. If it is captured by a human, there will be typos. So when you encounter a list like [1.0, 1.1, 1. 1.2,], well, is it [1.0, 1.1, 1.1.2,] or [1.0, 1.1, 1.3, 1.2,] where a 3 and a period would be missing. So Replace() anticipates that there will be no errors in the data set, just get rid of all the spaces wherever they are. If this is acceptable, then fine with me.
Nov 5 '10 #23
Sorry it's been so long everyone; I only get to work on this project when my chores are done. ;-)

Thanks, Desii, for the update.
Nov 11 '10 #24

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

9 posts views Thread by Mark | last post: by
7 posts views Thread by Chris Thunell | last post: by
3 posts views Thread by jwgoerlich | last post: by
24 posts views Thread by MU | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
reply views Thread by Marylou17 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.