473,327 Members | 2,090 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,327 software developers and data experts.

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 14561
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,834 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,834 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,834 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,834 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,834 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,834 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,834 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

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

Similar topics

3
by: RBalbat | last post by:
Hello all, I have encountered an unexpected problem where if I render a table (in IE or Opera) and there are cells that contains URLs with long query strings, even though I specify the table...
9
by: Mark | last post by:
I've run a few simple tests looking at how query string encoding/decoding gets handled in asp.net, and it seems like the situation is even messier than it was in asp... Can't say I think much of the...
7
by: Chris Thunell | last post by:
I'm looking to find in a long string an instance of 4 numbers in a row, and pull out those numbers. For instance: string = "0104 PBR", i'd like to get the 0104. string="PBR XT 0105 TD", i'd like...
2
by: comp.lang.php | last post by:
I am trying to replace within the HTML string $html the following: With Where I'm replacing "action=move_image" with "action=<?= $_REQUEST ?>"
3
by: jwgoerlich | last post by:
Hello group, I am working on a query string class. The purpose is to parse name-value pairs from incoming text. Currently, I am using the Regex code below. I have two questions. First, the...
2
by: Wayne Shu | last post by:
Hi everyone. In the following program, foo is an ambiguous call. #include <iostream> using namespace std; void foo(int *); void foo(int (&));
2
by: RiverstoneJebin | last post by:
Hi, I have 2 tables with same fields like event, venue, date and cost. Table 1 has 5 records and Table 2 has 100 records. How can i match a record from Table 1 with Table 2 to get the exact match...
2
by: Slippy27 | last post by:
I'm trying to modify a find/replace script which iterates through a file A and makes replacements defined in a csv file B. My original goal was to change any line in file A containing a search string...
24
by: MU | last post by:
Hello I have some code that sets a dropdownlist control with a parameter from the querystring. However, when the querystring is empty, I get an error. Here is my code: Protected Sub...
2
by: Neil Gilbert | last post by:
Ok i am fudging a legacy CRM system and i need to hide a button on the script using client side javascript. I am trying to hide a button on the screen based on the URL containing the word...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.