Create a Sequelize findAll where clause that returns all results regardless of punctuation in search-CodePudding

In a controller, using findAll(), I am trying to create a where clause to return all hits regardless of punctuation in the search string. Currently, punctuation is dictating what is returned even though i'm using the [Op.iLike] operator.

Example:

search can either be 'St Andrew' or 'St. Andrew'

In the following example: if search === 'St. Andrew' returns only results with the St. Andrew And, if search === 'St Andrew', it only returns the results with St Andrew

where: { name: { [Op.iLike]: `%${search}%` } },

In the following example: if search === 'St. Andrew' returns all intended results (results with or without the .) Howerver, if search === 'St Andrew', it only returns the results without the .

const noPunctuationSearch = search?.replace(/[^A-Za-z0-9] /g, ' ');

where: {
  name: {
    [Op.or]: [
      { [Op.iLike]: `%${search}%` },
      { [Op.iLike]: `%${noPunctuationSearch}%` },
    ],
  },
},

How do I set up the where clause and Operators to return all St. Andrew || St Andrew results regardless of the punctuation in search?

CodePudding user response：

You could use Op.iRegexp but you will need to massage the query a bit more to create a regular expression out of it.

const search = 'St. Andrews';

const noPuncuationSearch = search?.replace(/[^A-Za-z0-9] /g,'[^A-Za-z0-9] ');

where: {
  name: {
    [Op.iRegexp]: noPuncuationSearch,
  },
},