How do I use regex in loopback where clause - connected to a SQL Server datasource?

Question

I am using the below code to search users in the SQL Server DB but it throws REGEXP error.

Using loopback 3 and loopback-mssql-connecter

let searchedValue = await User.find({
        where: {
          or: [
            {first_name: new RegExp('\\b' + req.query.s, 'i')},
            {last_name: new RegExp('\\b' + req.query.s, 'i')},
            {email: new RegExp('^' + req.query.s, 'i')},
          ],
        },
      });

This exact query works when it is tied to a MongoDB datasource but doesn't work when using a SQL Server datasource.

I get the below errors

Microsoft SQL Server does not support the regular expression operator
Unhandled error for request GET /api/leagues/searchusers/?s=nir:
RequestError: Incorrect syntax near 'REGEXP'.

score 0 · Answer 1 · answered May 03 '19 at 22:56

0

Sql Server does not support regex. You can use LIKE, but no guarantees.

            {first_name: {like: '\\b' + req.query.s}},
            {last_name: {like: '\\b' + req.query.s}},
            {email: {like: '^' + req.query.s}}

MongoDB is a different datasource which does support them.

answered May 03 '19 at 22:56

Marvin Irwin

938
7
17

@marvin-irwin...Sorry for the delayed response. This approach did not work out. Any ideas on how I can implement a user search on a SQL Server Database? – Nirvik Dey May 24 '19 at 14:42

score 0 · Answer 2 · answered Mar 26 '20 at 14:28

0

Try this for SQL datasource

   [
        {first_name: { like: `%${req.query.s}%`} },
        {last_name: { like: `%${req.query.s}%`} },
        {email: { like: `${req.query.s}%`} },
   ]

answered Mar 26 '20 at 14:28

ASM

169
3
12

How do I use regex in loopback where clause - connected to a SQL Server datasource?

2 Answers2