Able Johnson Able Johnson - 7 months ago 13
SQL Question

How to Escape special characters in Microsoft SQL CE


NB: I am using Microsoft SQL Compact Edition 3.5


I have a table of users.I have the display name as user input and I need to query all the user whose display name matches the input.

select TOP (1) * from users where display_name like 'Abby Parker'


here
'Abby parker'
is the input

it is working fine in normal cases .But the problem is the display name can contain special characters

for eg display name can be
"Abby Park#er"
or simply
"%&^%&^%#%"

.The above query fails in such cases .I have already tried the solution specified here

Escaping special characters in a SQL LIKE statement using sql parameters

this is how I am building the query here

var command = ceConnection.CreateCommand();
command.CommandText = string.Format("select TOP (1) * from {0} where {1} like '[{2}]' ", tableName,fieldName, key);
}



  • {0}=>users

  • {1}=>display_name

  • {2}=>pattern



Thanks in advance

Answer

As posted here, please try the following:

var command = ceConnection.CreateCommand();
command.CommandText = string.Format("select TOP (1) * from {0} where {1} like @key ", tableName,
                    fieldName);
command.Parameters.AddWithValue("@key", key);
Comments