SQL Query wildcard search

Question

Im writing a C# web page thats tied to a listview. My client would like to be able to type in something or part of something that it would show results. For example he wants a textbox where he may put in a phone number, part of a phone number, a name, city or whatever and there would be a SP of sorts that finds then lists the info. How can I accomplish this in either a SQL Sp or within VS 2010?

Answer 1

SELECT cols FROM tbl WHERE field LIKE '%' + @input + '%'

Answer 2

As several others have suggested, use the LIKE operator.

However, do NOT just put the data the user typed in directly into your LIKE clause like others have suggested. This leads to a very simple and very dangerous vulnerability known as a SQL injection attack.

If you insert the user's input directly into

SELECT cols FROM tbl WHERE field LIKE '%' + input + '%'

then a user could put the following in the text box:

;DROP TABLE tbl; --

(as an example), which makes your SQL statement become:

SELECT cols FROM tbl WHERE field LIKE '%';  (the first part of your query)
DROP TABLE tbl;    (the injected sql that you don't want to let people run; drop the database table)
-- '%'   (the rest of your previous query is commented out)

Always make sure you used parametrised SQL statements, or at the minimum sanitize your inputs. You really don't want people to be able to run arbitrary SQL on your database server.

Jeff Atwood (of SO fame) has a short posting on this. And it is worth reading this too :)