LIKE OPERATOR PROBLEM (1 Viewer)

Teggun

Registered User.
Local time
Today, 12:13
Joined
Aug 30, 2019
Messages
33
Hello guys, I'm having a problem when using the like operator in a specific way I need. I wonder if someone could help me out with this.

The problem is I require the SQL to search a specific part in a string and can't figure out how to do so.

String example:
str = "blablabla 16x1 blablabla"

So I've been using the operator being the string "6x1" and it works nicely.
"LIKE '*" & str & "*'"

My problem is that it returns me more registers than needed as it captures "6x1,5", 16x1" as well, for example.

As this is always followed by a blank space, I tried to use:
"LIKE '*" & Chr(32) & str & Chr(32) & "*'"
"LIKE '* " & str & " *'"

But it seems to not been detecting the blank space with any of this ways.
Any ideas how I could solve this to only get back the specific part right in between blank spaces that matches exacly the string typed?

Thanks in advance.
 

Minty

AWF VIP
Local time
Today, 11:13
Joined
Jul 26, 2013
Messages
10,355
If it always has a space before and after try using Instr() and set some criteria on that. Something like;

WHERE Instr(1,[MySearchField], " "& StrSearch & " ") > 1
 

plog

Banishment Pending
Local time
Today, 06:13
Joined
May 11, 2011
Messages
11,613
Configure str correctly:

str=" 6x1 "

No need for special characters, just put the blanks in str
 

Mike Krailo

Well-known member
Local time
Today, 07:13
Joined
Mar 28, 2020
Messages
1,030
Did you read this recent thread HERE
It had to do with searching on a word delimited by spaces only. That might help with your particular problem as well.
 

GPGeorge

Grover Park George
Local time
Today, 04:13
Joined
Nov 25, 2004
Messages
1,776
This might seem obvious, but "Like" is intended to find all instances similar to the pattern, so you are going to get all results that have a similar pattern. If you want to find an exact match on one pattern, then a more precise method is called for.

Before we dig into that though, please clarify here:

it captures "6x1,5", 16x1"
Is that one string? If so, how does it work? You have delimiters on the string itself, before and after, but it also appears that there is an internal " which apparently is used for a different purpose.

We need to see really good sample data, multiple instances would be better.

Thanks.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 07:13
Joined
Feb 19, 2002
Messages
42,981
Once you get past this search issue, you might want to consider actually fixing the problem by extracting the size from the string and storing it separately.

I don't know how large your table will become but searching using Like and Instr() will require loading into memory and reading through every single row in the table. As long as there are only a few thousand rows, all will be fine but the search will start to slow eventually.

Once you separate the size, then Access will be able to use an index (provided you defined one) to find the records you want. As George mentioned, Like is designed as a fuzzy search so if you really want fuzzy results, you have no alternative but a RBAR (Row by Agonizing Row) search.
 

onur_can

Active member
Local time
Today, 04:13
Joined
Oct 4, 2015
Messages
180
Hello, your answer can be the following SQL statement.

Code:
SELECT Table1.id, Table1.producname
FROM Table1
WHERE (((Table1.producname) Like "*?6x1,?*")) OR (((Table1.producname) Like "*?6x1*"));
 

Users who are viewing this thread

Top Bottom