How to implement a SQL like 'LIKE' operator in java?

user_92813 0
Viewed 134968

I need a comparator in java which has the same semantics as the sql 'like' operator. For example:

myComparator.like("digital","%ital%");
myComparator.like("digital","%gi?a%");
myComparator.like("digital","digi%");

should evaluate to true, and

myComparator.like("digital","%cam%");
myComparator.like("digital","tal%");

should evaluate to false. Any ideas how to implement such a comparator or does anyone know an implementation with the same semantics? Can this be done using a regular expression?

wildcard sql regex java string
17 Answers

.* will match any characters in regular expressions

I think the java syntax would be

"digital".matches(".*ital.*");

And for the single character match just use a single dot.

"digital".matches(".*gi.a.*");

And to match an actual dot, escape it as slash dot

\.
Bob_450

Yes, this could be done with a regular expression. Keep in mind that Java's regular expressions have different syntax from SQL's "like". Instead of "%", you would have ".*", and instead of "?", you would have ".".

What makes it somewhat tricky is that you would also have to escape any characters that Java treats as special. Since you're trying to make this analogous to SQL, I'm guessing that ^$[]{}\ shouldn't appear in the regex string. But you will have to replace "." with "\\." before doing any other replacements. (Edit: Pattern.quote(String) escapes everything by surrounding the string with "\Q" and "\E", which will cause everything in the expression to be treated as a literal (no wildcards at all). So you definitely don't want to use it.)

Furthermore, as Dave Webb says, you also need to ignore case.

With that in mind, here's a sample of what it might look like:

public static boolean like(String str, String expr) {
    expr = expr.toLowerCase(); // ignoring locale for now
    expr = expr.replace(".", "\\."); // "\\" is escaped to "\" (thanks, Alan M)
    // ... escape any other potentially problematic characters here
    expr = expr.replace("?", ".");
    expr = expr.replace("%", ".*");
    str = str.toLowerCase();
    return str.matches(expr);
}
user_135310

Regular expressions are the most versatile. However, some LIKE functions can be formed without regular expressions. e.g.

String text = "digital";
text.startsWith("dig"); // like "dig%"
text.endsWith("tal"); // like "%tal"
text.contains("gita"); // like "%gita%"
user_576950

Every SQL reference I can find says the "any single character" wildcard is the underscore (_), not the question mark (?). That simplifies things a bit, since the underscore is not a regex metacharacter. However, you still can't use Pattern.quote() for the reason given by mmyers. I've got another method here for escaping regexes when I might want to edit them afterward. With that out of the way, the like() method becomes pretty simple:

public static boolean like(final String str, final String expr)
{
  String regex = quotemeta(expr);
  regex = regex.replace("_", ".").replace("%", ".*?");
  Pattern p = Pattern.compile(regex,
      Pattern.CASE_INSENSITIVE | Pattern.DOTALL);
  return p.matcher(str).matches();
}

public static String quotemeta(String s)
{
  if (s == null)
  {
    throw new IllegalArgumentException("String cannot be null");
  }

  int len = s.length();
  if (len == 0)
  {
    return "";
  }

  StringBuilder sb = new StringBuilder(len * 2);
  for (int i = 0; i < len; i++)
  {
    char c = s.charAt(i);
    if ("[](){}.*+?$^|#\\".indexOf(c) != -1)
    {
      sb.append("\\");
    }
    sb.append(c);
  }
  return sb.toString();
}

If you really want to use ? for the wildcard, your best bet would be to remove it from the list of metacharacters in the quotemeta() method. Replacing its escaped form -- replace("\\?", ".") -- wouldn't be safe because there might be backslashes in the original expression.

And that brings us to the real problems: most SQL flavors seem to support character classes in the forms [a-z] and [^j-m] or [!j-m], and they all provide a way to escape wildcard characters. The latter is usually done by means of an ESCAPE keyword, which lets you define a different escape character every time. As you can imagine, this complicates things quite a bit. Converting to a regex is probably still the best option, but parsing the original expression will be much harder--in fact, the first thing you would have to do is formalize the syntax of the LIKE-like expressions themselves.

Alan_M_209380

Java strings have .startsWith() and .contains() methods which will get you most of the way. For anything more complicated you'd have to use regex or write your own method.

job_436650
Dave_Webb_31710

Apache Cayanne ORM has an "In memory evaluation"

It may not work for unmapped object, but looks promising:

Expression exp = ExpressionFactory.likeExp("artistName", "A%");   
List startWithA = exp.filterObjects(artists);
Oscar_Reyes_206540

i dont know exactly about the greedy issue, but try this if it works for you:

public boolean like(final String str, String expr)
  {
    final String[] parts = expr.split("%");
    final boolean traillingOp = expr.endsWith("%");
    expr = "";
    for (int i = 0, l = parts.length; i < l; ++i)
    {
      final String[] p = parts[i].split("\\\\\\?");
      if (p.length > 1)
      {
        for (int y = 0, l2 = p.length; y < l2; ++y)
        {
          expr += p[y];
          if (i + 1 < l2) expr += ".";
        }
      }
      else
      {
        expr += parts[i];
      }
      if (i + 1 < l) expr += "%";
    }
    if (traillingOp) expr += "%";
    expr = expr.replace("?", ".");
    expr = expr.replace("%", ".*");
    return str.matches(expr);
}
user_1409430

I have a similar requirement, which may help, with some modifications, here is the code:

package codeSamplesWithoutMaven;

public class TestLikeInJava {

public static void main(String[] args) {
    String fromDb = "erick@gmail.com";
    String str1 = "*gmail*";
    String str2 = "*erick";
    String str3 = "*rick";
    String str4 = "*.com";
    String str5 = "erick*";
    String str6 = "ck@gmail*";
    System.out.println(belongsToStringWithWildcards(str1, fromDb));
    System.out.println(belongsToStringWithWildcards(str2, fromDb));
    System.out.println(belongsToStringWithWildcards(str3, fromDb));
    System.out.println(belongsToStringWithWildcards(str4, fromDb));
    System.out.println(belongsToStringWithWildcards(str5, fromDb));
    System.out.println(belongsToStringWithWildcards(str6, fromDb));
}

private static Boolean belongsToStringWithWildcards(String strToTest, String targetStr) {
    Boolean result = Boolean.FALSE;
    int type = 0; //1:start, 2:end, 3:both
    if (strToTest.startsWith("*") && strToTest.endsWith("*")) {
        type = 3;
    } else {
        if (strToTest.startsWith("*")) {
            type = 1;
        } else if (strToTest.endsWith("*")) {
            type = 2;
        }
    }
    System.out.println("strToTest " + strToTest + " into " + targetStr + " type " + type);
    strToTest = strToTest.replaceAll("[*]", "");
    System.out.println("strToTest " + strToTest + " into " + targetStr + " type " + type);
    switch (type) {
        case 1: result = targetStr.endsWith(strToTest);  
                break;
        case 2: result = targetStr.startsWith(strToTest);
                break;
        case 3: result = targetStr.contains(strToTest);
                break;
    }
    return result;
}

}

user_25593430 
public static boolean like(String source, String exp) {
        if (source == null || exp == null) {
            return false;
        }

        int sourceLength = source.length();
        int expLength = exp.length();

        if (sourceLength == 0 || expLength == 0) {
            return false;
        }

        boolean fuzzy = false;
        char lastCharOfExp = 0;
        int positionOfSource = 0;

        for (int i = 0; i < expLength; i++) {
            char ch = exp.charAt(i);

            // 是否转义
            boolean escape = false;
            if (lastCharOfExp == '\\') {
                if (ch == '%' || ch == '_') {
                    escape = true;
                    // System.out.println("escape " + ch);
                }
            }

            if (!escape && ch == '%') {
                fuzzy = true;
            } else if (!escape && ch == '_') {
                if (positionOfSource >= sourceLength) {
                    return false;
                }

                positionOfSource++;// <<<----- 往后加1
            } else if (ch != '\\') {// 其他字符，但是排查了转义字符
                if (positionOfSource >= sourceLength) {// 已经超过了source的长度了
                    return false;
                }

                if (lastCharOfExp == '%') { // 上一个字符是%，要特别对待
                    int tp = source.indexOf(ch);
                    // System.out.println("上一个字符=%，当前字符是=" + ch + "，position=" + position + "，tp=" + tp);

                    if (tp == -1) { // 匹配不到这个字符，直接退出
                        return false;
                    }

                    if (tp >= positionOfSource) {
                        positionOfSource = tp + 1;// <<<----- 往下继续

                        if (i == expLength - 1 && positionOfSource < sourceLength) { // exp已经是最后一个字符了，此刻检查source是不是最后一个字符
                            return false;
                        }
                    } else {
                        return false;
                    }
                } else if (source.charAt(positionOfSource) == ch) {// 在这个位置找到了ch字符
                    positionOfSource++;
                } else {
                    return false;
                }
            }

            lastCharOfExp = ch;// <<<----- 赋值
            // System.out.println("当前字符是=" + ch + "，position=" + position);
        }

        // expr的字符循环完了，如果不是模糊的，看在source里匹配的位置是否到达了source的末尾
        if (!fuzzy && positionOfSource < sourceLength) {
            // System.out.println("上一个字符=" + lastChar + "，position=" + position );

            return false;
        }

        return true;// 这里返回true
    }

Assert.assertEquals(true, like("abc_d", "abc\\_d"));
        Assert.assertEquals(true, like("abc%d", "abc\\%%d"));
        Assert.assertEquals(false, like("abcd", "abc\\_d"));

        String source = "1abcd";
        Assert.assertEquals(true, like(source, "_%d"));
        Assert.assertEquals(false, like(source, "%%a"));
        Assert.assertEquals(false, like(source, "1"));
        Assert.assertEquals(true, like(source, "%d"));
        Assert.assertEquals(true, like(source, "%%%%"));
        Assert.assertEquals(true, like(source, "1%_"));
        Assert.assertEquals(false, like(source, "1%_2"));
        Assert.assertEquals(false, like(source, "1abcdef"));
        Assert.assertEquals(true, like(source, "1abcd"));
        Assert.assertEquals(false, like(source, "1abcde"));

        // 下面几个case很有代表性
        Assert.assertEquals(true, like(source, "_%_"));
        Assert.assertEquals(true, like(source, "_%____"));
        Assert.assertEquals(true, like(source, "_____"));// 5个
        Assert.assertEquals(false, like(source, "___"));// 3个
        Assert.assertEquals(false, like(source, "__%____"));// 6个
        Assert.assertEquals(false, like(source, "1"));

        Assert.assertEquals(false, like(source, "a_%b"));
        Assert.assertEquals(true, like(source, "1%"));
        Assert.assertEquals(false, like(source, "d%"));
        Assert.assertEquals(true, like(source, "_%"));
        Assert.assertEquals(true, like(source, "_abc%"));
        Assert.assertEquals(true, like(source, "%d"));
        Assert.assertEquals(true, like(source, "%abc%"));
        Assert.assertEquals(false, like(source, "ab_%"));

        Assert.assertEquals(true, like(source, "1ab__"));
        Assert.assertEquals(true, like(source, "1ab__%"));
        Assert.assertEquals(false, like(source, "1ab___"));
        Assert.assertEquals(true, like(source, "%"));

        Assert.assertEquals(false, like(null, "1ab___"));
        Assert.assertEquals(false, like(source, null));
        Assert.assertEquals(false, like(source, ""));
user_109892750

Check out https://github.com/hrakaroo/glob-library-java.

It's a zero dependency library in Java for doing glob (and sql like) type of comparisons. Over a large data set it is faster than translating to a regular expression.

Basic syntax 

MatchingEngine m = GlobPattern.compile("dog%cat\%goat_", '%', '_', GlobPattern.HANDLE_ESCAPES);
if (m.matches(str)) { ... }
user_126227380

This's my take on this, it's in Kotlin but can be converted to Java with little effort:

val percentageRegex = Regex("""(?<!\\)%""")
val underscoreRegex = Regex("""(?<!\\)_""")

infix fun String.like(predicate: String): Boolean {

    //Split the text by every % not preceded by a slash.
    //We transform each slice before joining them with .* as a separator.
    return predicate.split(percentageRegex).joinToString(".*") { percentageSlice ->

        //Split the text by every _ not preceded by a slash.
        //We transform each slice before joining them with . as a separator.
        percentageSlice.split(underscoreRegex).joinToString(".") { underscoreSlice ->

            //Each slice is wrapped in "Regex quotes" to ignore all
            // the metacharacters they contain.
            //We also remove the slashes from the escape sequences
            // since they are now treated literally.
            Pattern.quote(
                underscoreSlice.replace("\\_", "_").replace("\\%", "%")
            )
        }

    }.let { "^$it$" }.toRegex().matches(this@like)
}

It might not be the most performant of all the solutions here, but it's probably the most accurate.

It ignores all the other Regex metacharacters other than % and _ and also supports escaping them with a slash.

user_68824560

Ok this is a bit of a weird solution, but I thought it should still be mentioned.

Instead of recreating the like mechanism we can utilize the existing implementation already available in any database!

(Only requirement is, your application must have access to any database).

Just run a very simple query each time,that returns true or false depending on the result of the like's comparison. Then execute the query, and read the answer directly from the database!

For Oracle db:

SELECT
CASE 
     WHEN 'StringToSearch' LIKE 'LikeSequence' THEN 'true'
     ELSE 'false'
 END test
FROM dual

For MS SQL Server

SELECT
CASE 
     WHEN 'StringToSearch' LIKE 'LikeSequence' THEN 'true'
     ELSE 'false'
END test

All you have to do is replace "StringToSearch" and "LikeSequence" with bind parameters and set the values you want to check.

user_29448950
Related