Emulating SQL LIKE in JavaScript

How can I emulate the SQL keyword LIKE in JavaScript?

For those of you who don't know what LIKE is, it's a very simple regex which only supports the wildcards %, which matches 0 or more characters, and _ which matches exactly one character.

However, it's not just possible to do something like:

var match = new RegEx(likeExpr.replace("%", ".*").replace("_", ".")).exec(str) != null;

...because the pattern might contain dots, stars and any other special regex characters.

What you have will work as long as you first escape the regex characters in your pattern. Below is one example from Simon Willison’s blog:

RegExp.escape = function(text) {
  if (!arguments.callee.sRE) {
    var specials = [
      '/', '.', '*', '+', '?', '|',
      '(', ')', '[', ']', '{', '}', '\\'
    ];
    arguments.callee.sRE = new RegExp(
      '(\\' + specials.join('|\\') + ')', 'g'
    );
  }
  return text.replace(arguments.callee.sRE, '\\$1');
}

You could then implement your code as:

likeExpr = RegExp.escape(likeExpr);
var match = new RegEx(likeExpr.replace("%", ".*").replace("_", ".")).exec(str) != null;

I was looking for an answer the same question and came up with this after reading Kip's reply:

String.prototype.like = function(search) {
    if (typeof search !== 'string' || this === null) {return false; }
    // Remove special chars
    search = search.replace(new RegExp("([\\.\\\\\\+\\*\\?\\[\\^\\]\\$\\(\\)\\{\\}\\=\\!\\<\\>\\|\\:\\-])", "g"), "\\$1");
    // Replace % and _ with equivalent regex
    search = search.replace(/%/g, '.*').replace(/_/g, '.');
    // Check matches
    return RegExp('^' + search + '$', 'gi').test(this);
}

You can then use it as follows (note that it ignores UPPER/lower case):

var url = 'http://www.mydomain.com/page1.aspx';
console.log(url.like('%mydomain.com/page_.asp%')); // true

NOTE 29/11/2013: Updated with RegExp.test() performance improvement as per Lucios comment below.

Here's a function I use, based on PHP's preg_quote function:

function regex_quote(str) {
  return str.replace(new RegExp("([\\.\\\\\\+\\*\\?\\[\\^\\]\\$\\(\\)\\{\\}\\=\\!\\<\\>\\|\\:\\-])", "g"), "\\$1");
}

So your line would now be:

var match = new RegEx(regex_quote(likeExpr).replace("%", ".*").replace("_", ".")).exec(str) != null;

An old question but there are actually no good answers here. TSQL LIKE expressions can contain square-bracket escaped sections that are already almost valid regular expressions and allow for matching % and _. E.g.:

'75%' LIKE '75[%]'
'[foo]' LIKE '[[]foo]' -- ugh

Here's my function to convert a LIKE expression into a RegExp. The input is split into square-bracket and non-square-bracket sections. The square-bracket sections just need backslash escaping and the non-square-bracket sections are fully escaped while the % and _ directives are converted to regular expressions.

const likeRegExp = (expression, caseSensitive = false) =>
    new RegExp(`^${
        expression.split(/(\[.+?\])/g)
        .map((s, i) => i % 2 ?
            s.replace(/\\/g, '\\\\') :
            s.replace(/[-\/\\^$*+?.()|[\]{}%_]/g, m => {
                switch(m) {
                    case '%': return '.*';
                    case '_': return '.';
                    default: return `\\${m}`;
                }
            })
        ).join('')
    }$`, caseSensitive ? '' : 'i');

If you want to use a regex, you can wrap each character of the string in square-brackets. Then you only have a few characters to escape.

But a better option might be to truncate the target strings so the length matches your search string and check for equality.

Emulating SQL LIKE in JavaScript

Related

Recent Posts