current community

your communities

more stack exchange communities

Stack Exchange
tour help
Take the 2-minute tour ×
Code Review Stack Exchange is a question and answer site for peer programmer code reviews. It's 100% free, no registration required.
up vote 2 down vote favorite
1

I have a bit of code that converts a Sql Like expression to a regex expression for the purposes of a Linq to objects Like extension method. For some time I have been using this conversion.

This conversion replaces all "%" with ".*?" which works for contains patterns but is over matching for starts with or ends with patterns. So the conversion of %abc to .*?abc is capturing both "abcdef" and "123abcdef".

I've amended the conversion algorithm to account for starts with and ends with LIKE expressions.

Here is my code:

internal static string ConvertLikeToRegex(string pattern)
{
    // Turn "off" all regular expression related syntax in the pattern string. 
    StringBuilder builder = new StringBuilder(Regex.Escape(pattern));

    // these are needed because the .*? replacement below at the begining or end of the string is not
    // accounting for cases such as LIKE '%abc' or LIKE 'abc%'
    bool startsWith = pattern.StartsWith("%") && !pattern.EndsWith("%");
    bool endsWith = !pattern.StartsWith("%") && pattern.EndsWith("%");

    // this is a little tricky
    // ends with in like is '%abc'
    // in regex it's 'abc$'
    // so need to tanspose
    if (startsWith)
    {
        builder.Replace("%", "", 0, 1);
        builder.Append("$");
    }

    // same but inverse here
    if (endsWith)
    {
        builder.Replace("%", "", pattern.Length - 1, 1);
        builder.Insert(0, "^");
    }

    /* Replace the SQL LIKE wildcard metacharacters with the
    * equivalent regular expression metacharacters. */
    builder.Replace("%", ".*?").Replace("_", ".");

    /* The previous call to Regex.Escape actually turned off
    * too many metacharacters, i.e. those which are recognized by
    * both the regular expression engine and the SQL LIKE
    * statement ([...] and [^...]). Those metacharacters have
    * to be manually unescaped here. */
    builder.Replace(@"\[", "[").Replace(@"\]", "]").Replace(@"\^", "^");

    return builder.ToString();
}

My initial units test are passing but not being overly conversant in regex syntax I am wondering if this is the best approach or if there are gaps in the conversion I am not seeing.

share|improve this question
    
What if it starts with and endsWith? I don't understand the need for startsWith to check that it doesn't also endWith –  Cruncher Dec 7 '13 at 16:16
    
LIKE '%abc%' (i.e. contains) converts to ".*?abc.*?" which captures abcdef and 123abcdef The regex ^abc$ (which is what would happen without those tests) only captures strings both starting and ending with abc. –  dkackman Dec 7 '13 at 16:26

3 Answers 3

up vote 6 down vote accepted

I think you're overcomplicating this and your code still doesn't work correctly. The LIKE pattern bcd shouldn't match abcde, but it does with your code.

What you should do is to always add ^ at the start and $ at the end.

This means the following conversions:

  • bcd → ^bcd$
  • %bcd → ^.*?bcd$
  • bcd% → ^bcd.*?$
  • %bcd% → ^.*?bcd.*?$

In the cases where the pattern starts with %, the ^ is not necessary (and similarly for $ and % at the end), but it also doesn't do any harm.

share|improve this answer
    
Even simpler than my suggestion. Nice! –  John Deters Dec 7 '13 at 17:12
    
Perfect. Works like a charm. I figured I was missing something much more straightforward. –  dkackman Dec 7 '13 at 17:22
    
nb: "?" is not needed since LIKE will always match the entire string anyway. –  Sylverdrag Dec 7 '13 at 19:54
    
@Sylverdrag Yeah, I think you're right, I just copied that from the code in the question. –  svick Dec 7 '13 at 20:10
up vote 3 down vote

Your name "startsWith" is confusing because it looks at both ends, and controls the placement of the trailing $. I'd simplify this:

bool startsWith = pattern.StartsWith("%") && !pattern.EndsWith("%");
bool endsWith = !pattern.StartsWith("%") && pattern.EndsWith("%");

if (startsWith)
{
    builder.Replace("%", "", 0, 1);
    builder.Append("$");
}
if (endsWith)
{
    builder.Replace("%", "", pattern.Length - 1, 1);
    builder.Insert(0, "^");
}

to this:

bool leadingLiteral = !pattern.StartsWith("%");
if (leadingLiteral)
{
    builder.Insert(0, "^");
}

bool trailingLiteral = !pattern.EndsWith("%");
if (trailingLiteral)
{
    builder.Append("$");
}

You may also note that I left the named Boolean variables in the code. I like using "explanatory variables" instead of comments. The optimizer will get rid of them in a production build, so they cost nothing to the runtime. But I think they make the code more readable and therefore more maintainable. They encourage you, the developer, to think about what you're doing. And they encourage you to think of an appropriate name. If you find it hard to name a thing, that may be a sign that it's unclear, or is doing too much.

share|improve this answer
up vote 2 down vote

In LIKE patterns, the interpretation of characters between square brackets is more literal than normal. What SQL dialect are you targeting? See, for example, the T-SQL documentation for LIKE (under Using Wildcard Characters as Literals). LIKE '5[%]' should be translated as regex '^5%$'.

share|improve this answer
    
I think a more natural (and equivalent) translation would be ^5[%]$. –  svick Dec 7 '13 at 18:14

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.