CODEWITHSUNDEEP
sqlmysqlmysql-error-1064
user349026
user349026

Reputation:

MySQL query problem with 'like' and confusion

I need to use a string query to make a DB search for a C# program that interacts with MySQL server. What I want to find is a name that is 'like' one of my other variables (nameVar)

I have the following query in a C# program

string q = "SELECT * 
              FROM TABLE 
             WHERE name is like %?nameVar%";

As soon as execute the query in my program I get a syntax error telling me that syntax near 'like' is incorrect. As soon as I remove the "%" sign, it works fine.

I am confused, is mandatory to remove the % sign while building a query string?

Upvotes: 1

Views: 340

Answers (5)

Raffael
Raffael

Reputation: 20045

I think you have to leave out 'is'.

MySQL Pattern Matching

Upvotes: 0

RichardTheKiwi
RichardTheKiwi

Reputation: 107696

Your parameter is replacing the ?nameVar part, including quotes. If the param is "TEST", your query gets presented as

string q = "SELECT * 
              FROM TABLE 
             WHERE name is like %'TEST'%";

As you can see, the % signs are out of place. either include them from the C# program into namevar, or change the query to

string q = "SELECT * 
              FROM TABLE 
             WHERE name is like '%' + ?nameVar + '%'";

Upvotes: 1

Mārtiņš Briedis
Mārtiņš Briedis

Reputation: 17752

I think the correct syntax is:

SELECT * FROM table WHERE fields LIKE '%phrase%'

Upvotes: 0

OMG Ponies
OMG Ponies

Reputation: 332521

Strings in SQL need to be enclosed in single quotes:

string q = "SELECT * 
              FROM TABLE 
             WHERE name LIKE '%?nameVar%' ";

Also, there's no IS operator when using LIKE.

Upvotes: 0

Derek Downey
Derek Downey

Reputation: 1532

you need to quote the query:

string q = "SELECT * from table where name is like '%?nameVar%'";

Upvotes: 0

Related Questions