C# SQLite Parameterized Select Using LIKE

Question

I am trying to do an SQL query such as

SELECT * FROM [TABLE] WHERE hostname LIKE '%myhostname%';

This works fine in plain SQL, but when I use System.Data.SQLite in C#, it only works with a literal, not a parameter, such as

string sel = "SELECT * FROM [TABLE] WHERE hostname LIKE '%@host%'";
...
command.Parameters.AddWithValue("@host", "myhostname");

This returns no results.

Mark Byers · Accepted Answer · 2010-12-01 22:52:22Z

35

You can't do that. The parameters must be complete values - it's not just a string substitution into the SQL. You could do this instead:

string sel = "SELECT * FROM [TABLE] WHERE hostname LIKE @host";
...
command.Parameters.AddWithValue("@host", "%myhostname%");

answered Dec 1, 2010 at 22:52

Mark Byers

844k202 gold badges1.6k silver badges1.5k bronze badges

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

5 Comments

Donnie Over a year ago

string sel = "SELECT * FROM [TABLE] where hostname like '%'+@host+'%'"; would also work.

AdamC Over a year ago

Thanks Mark - this helped a ton. Donnie - I tried this earlier and it didn't seem to work for me.

Chris Allen Lane Over a year ago

just encountered the same situation in Ruby with Sqlite3, and your solution worked like a champ there too. Upvote, which was for some reason met with a kick-line of ponies on my screen? :/

Thunderforge Over a year ago

@Donnie, your statement works, but I had to use concatenation with || instead of +

Naveen Kumar V Over a year ago

It Worked. Cool. :)

Community · Accepted Answer · 2017-05-23 12:34:31Z

18

Easiest way to do this is to use '||'

Use :

const string qry = "SELECT SiteNum FROM WorkTable WHERE WTName LIKE @wtName || '%' ";

Instead Of:

const string qry = "SELECT SiteNum FROM WorkTable WHERE WTName LIKE @wtName%";

I have already answered here

edited May 23, 2017 at 12:34

CommunityBot

11 silver badge

answered Aug 30, 2015 at 14:25

Kas

3,9635 gold badges31 silver badges57 bronze badges

1 Comment

Dan Roberts Over a year ago

Thanks for this! I was looking for a way to do it workout having to put the "%" characters into the search parameter value. I see that @Thunderforge commented on the accepted answer basically saying what you've said here, but I wouldn't have noticed that without seeing your answer first.

Collectives™ on Stack Overflow

C# SQLite Parameterized Select Using LIKE

2 Answers 2

5 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related