CODEWITHSUNDEEP
c#sql-servermodifier
Steve
Steve

Reputation: 672

How do you use the % modifier in a C# T-SQL query string?

I am trying to write a basic query in C# that includes the use of the '%' modifier.

Here is my query:

SELECT userName 
FROM tblUserInformation 
WHERE userName LIKE %@userNameQuery%

When I run that I get a SQL exception saying

You have a syntax error near @userNameQuery.

I have encountered similar differences with SQL queries and C# sql query strings before but am unsure how to resolve this one. I have tried:

SELECT userID 
FROM tblUserInformation 
WHERE userName LIKE '%'@userNameQuery'%'

The one below does not throw an exception but returns no results. It should because I can run the same query against the database directly and it returns many results.

SELECT userName 
FROM tblUserInformation 
WHERE userName LIKE '%@userNameQuery%'

Am I doing this correctly?

Also, @userNameQuery is properly filled in with SqlParameter and is the correct data type etc. Having tried both above and the second one works (returning no results) I am sure that there is no syntax error in my query string (extra semicolon or missing ").

Thank you for your time!

Upvotes: 0

Views: 548

Answers (3)

Alvaro Rodriguez
Alvaro Rodriguez

Reputation: 2848

I'd recommend using it like this:

  1. Write your query as SELECT userName from tblUserInformation WHERE userName LIKE @userNameQuery

  2. Make sure the string you bind as the parameter @userNameQuery contains the necessary %, i.e. declare it as

    var userName = @"%name%";

Upvotes: 1

HABO
HABO

Reputation: 15816

Please try:

like '%' + @userNameQuery + '%'

Upvotes: 3

Oded
Oded

Reputation: 499052

You need to pass in the % in the value of the parameter, not have it in the query itself.

SELECT userName from tblUserInformation WHERE userName LIKE @userNameQuery

Where @userNameQuery would be like %my query%.

Upvotes: 5

Related Questions