SQL LIKE Clause

[OldBirdman]

Thanks again ChipR. But I still don't get it. In post #6 you introduced 'regex' and from that point on I have not really seen how your suggestions apply to my problems.

'Regex' identifies patterns. It will tell me if my string matches a pattern or template. Although much more extensive than 'Like', it is still pattern matching. My problem is that if I know the pattern, I know the answer and do not need to use patterns. In my data, 'color' is the only spelling, and 'colour' will not find a match, so all input of 'colour' needs to be searched for as 'color'. For 'Karate Kid III', or K..K..3 either could be correct. So the table would be

Correct Alternate
Color   Colour
3       III
III     3

If I could have an SQL WHERE Name Like 'Karate Kid [3|III]' just as I have WHERE Name Like 'Gr[ae]y Jay', this would solve my problem. I could write a generic pre-search function to parse the string (to 'Grey' & 'Jay' or to 'Karate' & 'Kid' & 'III'), loop thru the words and see if any had alternates, and replace with pairings 'III|3|Three' or 'color|colour'. This looping will work for color/colour, but not for 3/III/Three' or 'Common/European/Eurasian/Northern'

[ChipR]

If I could have an SQL WHERE Name Like 'Karate Kid [3|III]' just as I have WHERE Name Like 'Gr[ae]y Jay', this would solve my problem.

I don't see the difference between what you are asking for and the SELECT statement in Post #10 that does exactly this. I'm not sure what you mean when you say looping doesn't work for 3/III/Three, it's just a different bit of code to assemble a string that will match each part.

[OldBirdman]

From post #11

User functions in SQL have been very slow, in my experience. SELECT Len([Name]) AS NameLength takes significantly less time than SELECT MyFunction([Name]) AS NameLength, no matter what code is in MyFunction()

responds to this.

Using DLookup or DCount is already slow enough. To put a user function into DLookup cannot speed it up. Normally, when processing user input I don't worry about speed, as most processing, i.e.'Sub textbox_OnChang e', because the typing cannot keep up with the computer. Here the response is already slow, even when pasting the entire name into a textbox, and not typing each letter and processing each.

[ChipR]

I think you're right, the use of the function is going to be slower than the SQL up to a certain point when you have a lot of alternates or substitutions. It would be the speed of
WHERE matchesRegEx("K arate Kid (3|Three|III)", [name])
versus
WHERE [name] = 'Karate Kid 3' OR [name] = 'Karate Kid Three' OR [name] = 'Karate Kid III'
I might do some testing eventually.

[OldBirdman]

For the moment, I'm going to put this on hold. Because with the birds I have Dates and Locations to deal with, as well as names, this is very involved. I will test various combinations for speed vs benefit, but am afraid that the human brain and some manual clicking can simplify the coding 90%.

For the movies, the queries are quite complex. I have a 'filter' dialog that may allow AND or OR for any or all of the following categories: Title(including multible), ReleaseYear or range, Seen(and Date), Rating(MPAA)(in cluding multible), Genre(including multible), Act[or|ess](including multible) , Director(includ ing multible), Description, Language, and so forth. I build this query in VBA and it may exceed 4K characters.
Here I might want all movies either starring Clint Eastwood, Directed by Clint Eastwood (but not his daughter) or Francis Ford Coppela or Stephen Spielberg (that name is mis-spelled, by the way), released since 2000, that I have not seen. Or all "R" or "PG-13" rated movies in the db not seen and in Drama or Romance.

Let's close this thread, and when I'm ready, I'll open a new thread "Using RegEx", coming soon to a forum near you.