How to Escape special characters in Microsoft SQL CE

Question

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

How are you building your query? Can you please post the C# code, or is this issue just in SQL? — Draken, Apr 25 '16 at 07:07
It seems that the question you have linked is the solution. What doesn't work for you in those answers? — Steve, Apr 25 '16 at 07:15
Try [this](http://stackoverflow.com/a/24319660/833070) instead, also prevents SQL injection attacks — Draken, Apr 25 '16 at 07:18
select TOP (1) * from accounts where display_name like '[Abby Parker]' is not matching any rows even though there is a row with display name "Abby Paniker" — Able Johnson, Apr 25 '16 at 07:23
Abby Parker is not equal to Abby Paniker, like needs % inserted into the string to know there could be other things around a value, read [this](http://www.w3schools.com/sql/sql_like.asp) — Draken, Apr 25 '16 at 07:24
Have you tried the code I posted below? Does it work if you use `=` instead of `like`? Why are you using `like` if you are passing the whole string anyway without `%`? — Draken, Apr 25 '16 at 07:50
Yes, It works! also thanks for pointing it out I have updated like with =.As a noobie in SQL I have a lot to learn — Able Johnson, Apr 25 '16 at 08:09

score 2 · Accepted Answer · edited May 23 '17 at 11:59

2

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);

edited May 23 '17 at 11:59

Community

1
1

answered Apr 25 '16 at 07:24

Draken

3,134
13
34
54

1

This works but with a small edit @tableName and @fieldName are not resolved properly in the query.But I understood your idea and applied for `@key` only it works! Thanks – Able Johnson Apr 25 '16 at 08:01

How to Escape special characters in Microsoft SQL CE

1 Answers1