What should be used to implement MySQL LIKE statement in MongoDB?

To implement MySQL LIKE statement functionality in MongoDB, use the $regex operator. This provides pattern matching capabilities similar to SQL's LIKE operator for text searches.

Syntax

db.collection.find({ "fieldName": { $regex: "pattern" } });

// Alternative syntax
db.collection.find({ "fieldName": /pattern/ });

Sample Data

db.likeInMongoDBDemo.insertMany([
    { "Name": "Sam" },
    { "Name": "John" },
    { "Name": "Scott" },
    { "Name": "Sean" },
    { "Name": "Samuel" }
]);

{
    "acknowledged": true,
    "insertedIds": [
        ObjectId("5cd6922857806ebf1256f123"),
        ObjectId("5cd6923157806ebf1256f124"),
        ObjectId("5cd6924557806ebf1256f125"),
        ObjectId("5cd6924f57806ebf1256f126"),
        ObjectId("5cd6925857806ebf1256f127")
    ]
}

Example 1: Contains Pattern (MySQL LIKE '%Sam%')

Find all names containing "Sam" ?

db.likeInMongoDBDemo.find({ "Name": { $regex: "Sam" } });

{ "_id": ObjectId("5cd6922857806ebf1256f123"), "Name": "Sam" }
{ "_id": ObjectId("5cd6925857806ebf1256f127"), "Name": "Samuel" }

Example 2: Starts With Pattern (MySQL LIKE 'S%')

Find names starting with "S" ?

db.likeInMongoDBDemo.find({ "Name": { $regex: "^S" } });

{ "_id": ObjectId("5cd6922857806ebf1256f123"), "Name": "Sam" }
{ "_id": ObjectId("5cd6924557806ebf1256f125"), "Name": "Scott" }
{ "_id": ObjectId("5cd6924f57806ebf1256f126"), "Name": "Sean" }
{ "_id": ObjectId("5cd6925857806ebf1256f127"), "Name": "Samuel" }

Example 3: Case-Insensitive Search

Find names containing "sam" regardless of case ?

db.likeInMongoDBDemo.find({ "Name": { $regex: "sam", $options: "i" } });

{ "_id": ObjectId("5cd6922857806ebf1256f123"), "Name": "Sam" }
{ "_id": ObjectId("5cd6925857806ebf1256f127"), "Name": "Samuel" }

Key Points

• ^ matches the beginning of the string (equivalent to MySQL LIKE 'pattern%')
• $ matches the end of the string (equivalent to MySQL LIKE '%pattern')
• $options: "i" makes the search case-insensitive
• No anchors means contains pattern (equivalent to MySQL LIKE '%pattern%')

Conclusion

The $regex operator in MongoDB effectively replaces MySQL's LIKE statement. Use pattern anchors and options to control matching behavior and achieve the exact search functionality you need.