Convert SQL LIKE to MongoDB $regex for Pattern Matching

LIKE to $regex

SQL's LIKE operator performs pattern matching with two wildcards: % (any sequence of characters) and _ (any single character). MongoDB uses $regex with regular expressions for the same purpose.

Example SQL

SELECT * FROM users
WHERE name LIKE 'John%'

Generated MongoDB Query

db.users.find({
  name: { $regex: /John.*/i }
})

Wildcard Conversion Table

SQL LIKE Pattern	MongoDB Regex	Matches
'John%'	/John.*/	Starts with "John"
'%smith'	/.*smith/	Ends with "smith"
'%admin%'	/.*admin.*/	Contains "admin"
'J_n'	/J.n/	"J" + any char + "n"
'%test_'	/.*test./	Contains "test" + one more char

Case Sensitivity

The converter adds the i flag for case-insensitive matching, which mirrors the default behavior of LIKE in many SQL databases (MySQL, SQL Server). PostgreSQL's LIKE is case-sensitive by default; use ILIKE there for case-insensitive matching.

NOT LIKE

SQL's NOT LIKE is converted using MongoDB's $not operator wrapping the $regex:

db.users.find({
  name: { $not: { $regex: /admin.*/i } }
})

Performance Impact

Regex queries in MongoDB that do not start with a literal prefix (i.e., patterns starting with % / .*) cannot use indexes efficiently and will trigger a collection scan. For production use with large datasets, consider using MongoDB's text indexes or Atlas Search for full-text search capabilities.