Using a wildcard in Database Search

Ask the community
,
1

Is there a reasonably simple way to include a wildcard (e.g. an asterisk) in a database search string?

I’ve managed to create a working search form for a database collection, but would like to search the applicable fields with two wildcards. One of these would be an asterisk (*) to represent an unknown number of missing letters; the other would be a question mark (?) to represent a single unknown letter.

For information the database collection relates to ancient/medieval coins whose inscriptions are often only partially legible due to wear or the coin being incomplete. By entering a few surviving letters and using the above wildcards, the intention is to identify the coin by comparing the surviving details with full inscriptions from published sources. This project is an attempt to convert an M/S Access database I created many years ago to a web-based version.

Thank you in advance for any advice.

2

Hi Roderick,

Currently our Database search does not support that, feel free to post a feature request here .

In the meantime, you can achieve the filtering using Javascript code.
Have a look at Regular Expressions . You have got two alternatives:

  1. Page the data using dataset next() method and match each of the records.
  2. Bring all the data at once and then match the regular expression.

Hope this helps,

Liran.

3

Thanks for reply. I’ll take a look at your suggestions.

4

With a little ‘family help’, this query has now been resolved. The generic code below will search a specified database field using wildcard ? to represent any single letter, and * to represent multiple letters.

import wixData from ‘wix-data’;

$w.onReady(function () {
// Wait for data to upload;
});

export function button1_click(ev) {
// Get the query from the html form:
let query = $w(“#input1”).value;

// Sanitise the query to replace RegEx control characters. Not really
// necessary at the moment, but if you added any other search
// characters (such as +), then the query would need to be cleaned prior to
// creating the regex…
let sanitizedQuery = query.replace(/?/g, “@1”) // ? matches single character
.replace(/*/g, “@2”); // * matches any number of characters

// Replace sanitized query with RegEx equivalents. \S matches any
// non-space character.
let regex = new RegExp(sanitizedQuery.replace(/@1/g, “\S{1}”, “gi”)
.replace(/@2/g, “\S*”, “gi”));

// Fetch everything, then filter in JS (terribly inefficient, but if
// Wix db doesn’t support wildcards, then not much choice)
wixData.query(“your-database-name”)
.find() // get all records
.then( (result) => {
var filteredItems = ;

  // Loop through every record, and match it against the regex pattern. 
  // If it matches, add it into the filteredItems array 
  for (var i = 0; i < result.length; i++) { 
    let item = result.items[i]; 

    // Here, "title" is the field key used in your database 
    if (regex.test(item.title)) { 
      filteredItems.push(item); 
    } 
  } 

  // filteredItems now contains only matching items, so return them to 
  // wix / table on page. 
  $w("#table1").rows = filteredItems; 

}) 
.catch( (error) => { 
  let msg = error.message; 
  let code = error.code; 

  console.error("Error running query: " + msg + "(" + code + ")"); 
});

}

Thanks to https://www.chrisblunt.com for the help.

5

Amazing! thanks for sharing!