2

Is there a way to use a LIKE statement using an ODBC connection in .net? I have tried everything I could think of, yet I always get a SQL exception. 

SELECT field FROM table WHERE fieldName LIKE '%SOME_STRING%';

string sql = "SELECT field FROM table WHERE fieldName like '%?%';

I'm using Sybase SQL Anywhere 11.

Danny Beckett
  • 20,529
  • 24
  • 107
  • 134
Dan
  • 1,222
  • 2
  • 17
  • 33
  • 2
    What's the exception and what type of database are you connecting to? That is much more relevant than whether or not you're using ODBC. – David Mar 01 '11 at 20:32
  • posting some code and exception message would greatly increase the chance of someone helping you – Kris Ivanov Mar 01 '11 at 20:34
  • Updated my question with code sample. The basic problem is that is always treated like a string literal. Couldn't find any examples that did this at all. – Dan Mar 01 '11 at 20:42

1 Answers1

3

You can use the LIKE statement as long as the database you're connecting to supports it. Whether you use ODBC or not shouldn't have anything to do with it.

One gotcha to watch out for is that different databases use different wildcards, and different syntax. (For example, MS Access uses the "*" as the wildcard, while SQL Server uses "%").

http://office.microsoft.com/en-us/access-help/like-operator-HP001032253.aspx

http://msdn.microsoft.com/en-us/library/ms179859.aspx

At any rate, look at the documentation for the database you're connecting to to see if it supports the LIKE statement, and the proper syntax.

or take a look at the answers here: Parameterized Queries with LIKE and IN conditions

Edit

I see you're trying to use an input parameter as part of the LIKE clause. I can't find documentaiton on this specific to Sybase, but even in the SQL Server side we need to (unfortunately) contencate the string in this case. I realize that this is bad from a SQL injection point of view, but I don't believe there is a way around it, so you'lll have to fall back on sanitizing/escaping the string.

Here's a post onn doing it in SQL Server. Hopefully this can translate into something that will work with Sybase ASA. http://palisade.plynt.com/issues/2006Jun/injection-stored-procedures/

As a last resort, you can use this:

string sql = "SELECT field FROM table WHERE fieldName like '%" + SOMESTRING.Replace("'", "''") + "%';
Community
  • 1
  • 1
David
  • 72,686
  • 18
  • 132
  • 173
  • I know the syntax in SQL, but it's about building the string / parameters that seems incorrect. – Dan Mar 01 '11 at 20:35
  • OK. Code snippet? Maybe it's something in the code that's being overlooked? - and the actual error message would help as well. – David Mar 01 '11 at 20:37
  • see also http://stackoverflow.com/questions/583336/how-do-i-create-a-pdo-parameterized-query-with-a-like-statement – sdjuan Nov 08 '16 at 19:35