Thursday, July 2, 2009

Implementing a SQL-like 'LIKE' Comparison in VB.NET

I searched the net for some time to find an easy way to implement the logic in the SQL LIKE in a VB.NET function. There were many over simplified examples. Then I found this elegant solution using regular expressions (regex). The original is found here in C#:

http://bytes.com/groups/net-c/253519-using-regex-create-sqls-like-like-function


I have provided a VB.Net version below:


Public Function IsSqlLikeMatch(ByVal input As String, ByVal pattern As String) As Boolean

' Turn "off" all regular expression related syntax in
' the pattern string.


pattern = Regex.Escape(pattern)

' Replace the SQL LIKE wildcard metacharacters with the
' equivalent regular expression metacharacters.


pattern = pattern.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.


pattern = pattern.Replace("\[", "[").Replace("\]", "]").Replace("\^", "^")

Return Regex.IsMatch(input, pattern)

End Function

3 comments:

Anonymous said...

This won't work well.
The code changes all occurrence of %, _, ? but it shouldn't inside of a group.
If you want to match [a-b_%] then it converts to [!a-b..*?] which is obviously not the same.

Anonymous said...

Sorry, prev commend had some mistakes.
Fixed version:

This won't work well.
The code changes all occurrence of %, _, ? but it shouldn't inside of a character group.
If you want to match [_%] then it converts to [..*?] which is obviously not the same.
So want to mach "_" or "%" but it will match "." or "*" or "?"

Anonymous said...

Sorry, prev commend had some mistakes.
Fixed version:

This won't work well.
The code changes all occurrence of %, _, ? but it shouldn't inside of a character group.
If you want to match [_%] then it converts to [..*?] which is obviously not the same.
So want to mach "_" or "%" but it will match "." or "*" or "?"