issues with finding data with certain strings

Question

I am trying to make this work but have been unlucky it is giving me th following error

Error in Like operator: the string pattern '%testing : | / - “ ‘ & * # @%' is invalid.

This search works as long as it doesnt contain a string as the one above.. This is my code for the search

DataRow[] rows = GetAllItems.Select("Name like '%" + cleanedText + "%'");

I tried - Modified per cHaos (still errors though)

  string cleanedText =  SearchText.Replace("\"", "\\\"").Replace("'", "''");

but no luck when i enter the following string in the search although i know it is in the data

testing : | / - “ ‘ & * # @%

Anyone has a nice suggestion

Thank you

Ummm... what does this do? sSearctText.Replace("\"", "\"")? — Mark Byers
– Mark Byers, Commented Oct 13, 2011 at 19:34
You do realize that .Replace("\"", "\"") does nothing useful, right? Same with all the other backslashed replacements -- you're replacing wacky chars with the exact same char. You probably want .Replace("\"", "\\\"") and the like. — cHao
– cHao, Commented Oct 13, 2011 at 19:35
sSearchText is the original variable - cHao, i used that Replace("\"", "\"") and it actually fixed the issue of not finding items with " in it.. dunno why, but i see your point — user710502
– user710502, Commented Oct 13, 2011 at 19:38
@cHao & Mark I changed sSearctText.Replace("\"", "\"")? to sSearctText.Replace("\"", "\\\"")? and still the same error. — user710502
– user710502, Commented Oct 13, 2011 at 19:45

Community · Accepted Answer · 2020-06-20 09:12:55Z

3

Both % and * can be used interchangeably for wildcard characters in the LIKE comparison.

As such you will need to enclose these characters in square brackets in your expression string like so:

Name like '%testing : | / - “ ‘ & [*] # @%'

See DataColumn.Expression.

From the above link:

Wildcard Characters

Both the * and % can be used interchangeably for wildcard characters in a LIKE comparison. If the string in a LIKE clause contains a * or %, those characters should be enclosed in brackets ([]). If a bracket is in the clause, each bracket character should be enclosed in brackets (for example [[] or []]).

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Oct 13, 2011 at 20:04

jdavies

12.9k3 gold badges35 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

issues with finding data with certain strings

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related