Results 1 to 1 of 1
  1. #1
    Join Date
    Jun 2010

    Question Unanswered: SQLite: wildcard in column data

    I have a SQLite database containing a table with a lot of "rules" which are strings with wildcards in them. I'd like to match input against these rules and return 0:n results. I'm looking for ideas on the best and fastest way to achieve this.

    Some details:
    • Rules are actually URL's with wildcards meaning they are case sensitive (other than the wildcard portion of course) and have a strict encoding (think US-ASCII)
    • Input, is a real world URL
    • I only need to support the '*' (or '%') wildcard.
    • I'm not sure if this helps, but it's fine for the rules to allow wildcards only per "part". For example, "*" and "*" would be allowed, but "ww*" and "*match" would not.
    • I'd like to return all matches for a given input URL. Ideally they would be sorted by "most greedy" logic, but I can do this in code.
    • I'm not opposed to creating a custom function(s) as I control the SQLite code itself. Perhaps something such as SELECT u FROM rules WHERE u URL_MATCH('')

    SQLite seems to allow for LIKEing when the wildcard(s) are in a column, but what I'm trying to avoid is a dumbed down "scan the entire table" scenario as the rule count may be fairly large. As for keeping it in the database (vs say a completely in memory tri tree), I'm limited in the memory I can take up full time.

    Any suggestions/etc. very appreciated! If I need to provide any additional information please let me know.

    EDIT: Mods, please move this thread to the PC Database forum. I could have sworn that's where I was at.
    Last edited by bashby; 06-28-10 at 23:03.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts