Wildcards, search engines, and more...
 

If you search a column in a database for "6648-8%", and the column contains a record "6648-84U", that record will match, and the row will be returned.

This much I know.

How can I do it the other way? I want to be able to specify that a record should match "6648-8%", where that is actually the search string.

I've tried simply putting that in as the record...while that would make sense, it doesn't work.

I'm having very little luck with Google here, mainly because I'm unsure what to search for.

MPEDrummer

Try preceding the % sign with a backslash; ( \% ) It's the standard way to show that you want to literaly display ("escape") a special character.

Cheers.

Hmm...perhaps I'm unclear...

I want the user to be able to search for the specific 6648-84U and have a record MATCH 6648-8%. So the user could enter any specific model number, and match the broader information as well.

Without entering everything as individual keywords.

MPEDrummer

Perhaps parse the input before passing it to the search, to chop off the last character and have it be replaced with a wildcard (a little string manipulation), and then pass that to the search.

Could work.

brakets? quotes?

its character 37 if you can input a character.

Well, here you get the problem of not knowing where to cut off the search and put in the %. If there's a clear way to know where to cut it, then just chop that bit off and add in the %. For example, if you always want to cut off the last 2 digits, or if you always cut starting from the last number, etc. If there isn't a clear way to cut it, you'd need to specify specific rules depending on the type of id. This could be very, very tedious. It might be better to just leave that kind of searching to the user - they probably know better than the program anyway.

It's also possible to run it through a regex, ie:

If it matches the pattern of dddd-dd[.], then chop off the last digit and replace it with a %, otherwise match it with a different pattern.

The problem there, though, is that then information specific to the 6648-84T would be returned.

I think you're on the right track.

I'm going to play with this a bit:
and we'll go from there.

MPEDrummer

Wouldn't this return EVERY book, since you'd eventually get to all *s?

Nope. What I'd be doing is backwards...I'd be searching for the LITERAL *, not * as a wildcard. Then, for entries that will apply to all of a series, such as the entire 800 series 6648, I could make the model keyword 6648-8**.

This results in 1 single keyword that matches all 1296 possible 2-alphanumeric-character combinations that could be after the 8.

Maybe I'm still confused about what your problem is, but since you seem to have it solved I won't worry about it.

If you want a "fast" solution, try backreferences.

Ie, in the 6648-84U entry there is a reference to 6648-8% and all other wildcard entries that match it.

Keep a table of all "wildcard" entries in the db, like 6648-8%.

If someone enters something not in the DB, you can either decide you 'should' fail, or you can decide to do a search only through the "wildcard" entries.

The second use of the "wildcard"-only table is when you add a new normal entry to the DB. You'll have to check it against all existing "wildcard" entries and insert the correct backreferences.

When you add a "wildcard" entry, you can simply search for it, and insert the required backreferences.

This would allow for "wildcard" entries like
66%-8%U
which none of the other proposals deal with reasonably.

(I assume you know how to have an arbitrary number of backreferences in a db: use a linked list)

Well, here I am, day late and a dollar short, but I figured it out.

The SQL function REGEXP can accept a stored column as either argument. By using the stored column as the regular expression, and not what I'm matching against, it's a snap :)

MPEDrummer