Wildcard value inside string throws EvaluateException in Like operator condition-CodePudding

Dim insertedrows() As datarow = mModel.WorkTable.select(tableitem.datagridstatuscolumn   "= 'i' and c_commodity_code like '%"   translatestring.checkandconvertstringwithapostrophe(ccCode)   "'")

here my cccode has a value of 'P_BRE*L5k', while passing the value throws error

CodePudding user response：

The code above throws an EvaluateException with the message Like operator string model '%P_BRE*L5k' is invalid.

According to Docs you cannot have an asterisk inside the string evaluated by the LIKE operator unless you enclose that character (and others) inside an open and close square bracket.

To match the special characters left bracket ([), question mark (?), number sign (#), and asterisk (*), enclose them in brackets.

So, if your data contains these characters you need a special purpose method to replace them with the proper escaping method

Public Function EscapeStringForLike(input As String) As String
    input = input.Replace("[", "[[]")
    input = input.Replace("*", "[*]")
    input = input.Replace("?", "[?]")
    input = input.Replace("#", "[#]")
    Return input
End Function

Notice that the first replace should be the one to escape the open bracket otherwise you could end up replacing the brackets added by the previous escaping.

Call it in this way

Dim search = EscapeStringForLike(translatestring.checkandconvertstringwithapostrophe(ccCode))
Dim insertedrows() As datarow = mModel.WorkTable.select(tableitem.datagridstatuscolumn   "= 'i' and c_commodity_code like '%"   search   "'")