Parameterized LIKE clause in SQL statement using Dapper

Question

I want to perform the following query using Dapper, which currently doesn't return expected results (I think it must be treating the @pName param as literal text within the single quotes?):

var q = "SELECT * FROM Users WHERE Name LIKE '@pName%'";

@pName is the param I assign a value to upon executing the query.

Things work if I just build the SQL like:

var q = "SELECT * FROM Users WHERE Name LIKE '" + name + "%'";

.. but I would prefer to use a param if possible.

I am executing the query using the following code:

o = _cn.Query<User>(q, new { pName = new DbString { Value = name, IsFixedLength = false, Length = 25, IsAnsi = true } }).ToList();

How do I got about this using Dapper?

make sure you remember to escape your like clause (see comment on answer) — Sam Saffron, Sep 22 '11 at 23:59

Marcelo Cantos · Accepted Answer · 2014-09-13T03:06:45.150

18

SELECT * FROM Users WHERE Name LIKE @pName + '%'

edited Sep 13 '14 at 03:06

answered Sep 22 '11 at 08:55

Marcelo Cantos

181,030
38
327
365

3

Make sure you escape your like clause though: http://stackoverflow.com/questions/439495/tsql-like-escape-clause – Sam Saffron Sep 22 '11 at 23:59
Thanks Sam. I wasn't familiar with this so will add it. – marcusstarnes Oct 03 '11 at 09:58
Buggered if I know. Someone else did that as an edit. I rolled it back. – Marcelo Cantos Sep 13 '14 at 03:06

score 1 · Answer 2 · answered Oct 18 '22 at 16:56

I would like to add here another possible solution:

var results = cn.Query("SELECT * FROM Table WHERE Column LIKE @value", new { value = value + "%" });

The wildcard is inside the string var itself, and then we reference that var in the SQL. Applies to any wildcard pattern you want.

Parameterized LIKE clause in SQL statement using Dapper

2 Answers2