CODEWITHSUNDEEP
javascriptmysqlnode.jssequelize.js
anosha_rehan
anosha_rehan

Reputation: 1600

Similar string matching in Sequelize MYSQL

I'm new to Sequelize and JS and might not know all operators so I want to ask if there's any way to retrieve an entry from a database which is similar to but not an exact match. So for example the string I'm searching for is +921234567890 but in the database its stored as +92-1234567890, separated with a hyphen for country dialing codes. The db I'm using is MySQL. I tried this which I know is incorrect but I'm not sure how to move ahead:

 where: {
        cell: {
          [Op.or]: {
            [Op.like]: "%-%",
            [Op.eq]: cellNumber,
          },
        },

Upvotes: 1

Views: 1856

Answers (2)

anosha_rehan
anosha_rehan

Reputation: 1600

The solution to this issue was solved using raw sql query but for anyone looking for an answer when using REGEXP_REPLACE, you should use REPLACE with sequelize.literal:

cell: { [Op.eq]: this.sequelize.literal("REPLACE('cell','-','')") }

Upvotes: 1

doublesharp
doublesharp

Reputation: 27607

You can use the REGEX_REPLACE() sql function to replace any non-numeric characters in the cell column before comparing to your input. Use the sequelize.where(), sequelize.fn(), and sequelize.col() functions to generate the query. Assuming your table is model and Model is Model.

const cellNumber = 1234567890;

const results = await Model.findAll({
  where: sequelize.where(
    sequelize.fn('REGEXP_REPLACE', sequelize.col('model.cell'), '[a-zA-Z]+', ''),
    '=',
    cellNumber
  ),
});

This should generate SQL similar to:

SELECT * FROM `model`
WHERE REGEXP_REPLACE(`model`.`cell`, '[a-zA-Z]+', '') = 1234567890;

Upvotes: 2

Related Questions