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

Question

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!

I strongly recommend to use SQL Parameters of the SQL Command. It keeps culture things. csharp-station.com/Tutorial/AdoDotNet/lesson06 — NoWar
– NoWar, Commented Aug 10, 2012 at 19:02

Oded · Accepted Answer · 2012-08-10 19:00:24Z

5

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%.

answered Aug 10, 2012 at 19:00

Oded

501k102 gold badges900 silver badges1k bronze badges

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

1 Comment

Steve Over a year ago

This works, thank you kind sir. I will mark as correct in 6 minutes :D

HABO · Accepted Answer · 2012-08-10 19:01:06Z

3

Please try:

like '%' + @userNameQuery + '%'

answered Aug 10, 2012 at 19:01

HABO

15.9k5 gold badges44 silver badges61 bronze badges

Comments

Gonzalo.- · Accepted Answer · 2012-08-10 19:11:41Z

1

I'd recommend using it like this:

Write your query as SELECT userName from tblUserInformation WHERE userName LIKE @userNameQuery
Make sure the string you bind as the parameter @userNameQuery contains the necessary %, i.e. declare it as

var userName = @"%name%";

edited Aug 10, 2012 at 19:11

Gonzalo.-

12.7k5 gold badges53 silver badges84 bronze badges

answered Aug 10, 2012 at 19:00

Alvaro Rodriguez

2,8483 gold badges33 silver badges40 bronze badges

Collectives™ on Stack Overflow

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

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related