CODEWITHSUNDEEP
c#sql
P a u l
P a u l

Reputation: 7911

c#: what is the proper way to perform sql LIKE searches using any characters

I'm building (c#) sql select commands strings on the fly using LIKE %somestring% conditions. In my search strings I want to be able to handle any character found on a standard PC (US) keyboard (including ~ ! @ # % etc., alt-special chars not required but would be nice to have). I know that single quotes need to be doubled up and perhaps double quotes as well. What other string fixes might be required to ensure correct syntax?

Upvotes: 0

Views: 506

Answers (2)

Kristen
Kristen

Reputation: 4291

Apart from doubling up single quotes (or using a parametrised query), will the user know that "_" and "%" are wildcards (any-character and zero-or-more-any-characters respectively), and that "[...]" creates a closure?

To escape those characters there are two routes

WHERE Foo LIKE '%xxx\%yyy%' ESCAPE '\'

or WHERE Foo LIKE '%xxx[%]yyy%'

the second uses the side effect of creating a closure, and avoids having to use the ESCAPE (which in itself needs some thought to choose a character that does not conflict with the rest of the string, or is itself escaped where it occurs)

Note that using 'LIKE %somestring%' will usually require a table scan, and may therefore lead to performance problems - e.g. if you have millions of rows to be checked.

In SQL Server you can use sp_ExecuteSQL if you are generating WHERE clauses by string-concatenation - so that only fields that the user specifies criteria for are included. sp_ExecuteSQL will cache the query and, most times, improve performance. (please ask if that it relevant and you need help)

Upvotes: 2

Anton Gogolev
Anton Gogolev

Reputation: 115749

No fixes required:

SqlCommand cmd = new SqlCommand("select * from Foo where Bar like @p", connection);

SqlParameter p  = new SqlParameter();
param.ParameterName = "@p";
param.Value = pattern;

cmd.Parameters.Add(param);

Upvotes: 5

Related Questions