Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to use wildcards in SQL query with parameters

Tags:

c#

sql

Say I have a basic query, something like this:

 SELECT holiday_name
 FROM holiday
 WHERE holiday_name LIKE %Hallow%

This executes fine in my sql query pane and returns 'Halloween'. My problem occurs when I try to use parameters with with the wildcard '%' characters in my code.

SqlConnection Connection = null;
SqlCommand Command = null;

string ConnectionString = ConfigurationManager.ConnectionStrings["SQLdb"].ConnectionString;
string CommandText = "SELECT holiday_name "
                   + "FROM holiday "
                   + "WHERE holiday_name LIKE %@name%";
Connection = new SqlConnection(ConnectionString);

try
{
      Connection.Open();
      Command = new SqlCommand(CommandText, Connection);
      Command.Parameters.Add(new SqlParameter("name", HolidayTextBox.Text));
      var results = Command.ExecuteScalar();
}

catch (Exception ex)
{   
     //error stuff here       
}

finally
{
    Command.Dispose();
    Connection.Close();
}

This throws an incorrect syntax error. I've tried moving the '%' to my parameter like so

Command.Parameters.Add(new SqlParameter("%name%", HolidayTextBox.Text));

but then I receive an error saying I haven't declared the scalar variable '@name'. So, how do you properly format wildcard characters to be included with query parameters? Any help is appreciated!

like image 351
ovaltein Avatar asked Nov 01 '13 16:11

ovaltein


People also ask

What is like %% in SQL?

The LIKE operator is used in a WHERE clause to search for a specified pattern in a column. There are two wildcards often used in conjunction with the LIKE operator: The percent sign (%) represents zero, one, or multiple characters. The underscore sign (_) represents one, single character.

Can you use wildcard characters as part of criteria query?

To use wildcard characters in query criteria in Access, open the desired query in query design view. Then click into the “Criteria” row underneath the field into which to add the criteria. Type the criteria for which to search, using the appropriate wildcard characters.


3 Answers

whatever you do don't do this:

string CommandText = "SELECT holiday_name "
                   + "FROM holiday "
                   + "WHERE holiday_name LIKE '%'" + HolidayTextBox.Text + "'%'";

as that will open you up to sql injection, instead do this:

Command.Parameters.Add(new SqlParameter("@name", "%" + HolidayTextBox.Text + "%"));

you may like to know about Command.Parameters.AddWithValue, e.g:

Command.Parameters.AddWithValue("@name", "%" + HolidayTextBox.Text + "%");
like image 23
gordy Avatar answered Oct 20 '22 01:10

gordy


First off, your SqlParameter name is @name not name.

Second, I would move your wildcards.

So it would look like this:

string CommandText = "SELECT holiday_name "
               + "FROM holiday "
               + "WHERE holiday_name LIKE @name;"
Connection = new SqlConnection(ConnectionString);

try
{
  var escapedForLike = HolidatyTextBox.Text; // see note below how to construct 
  string searchTerm = string.Format("%{0}%", escapedForLike);
  Connection.Open();
  Command = new SqlCommand(CommandText, Connection);
  Command.Parameters.Add(new SqlParameter("@name", searchTerm));
  var results = Command.ExecuteScalar();
}

Note that LIKE requires special care when passing parameters and you need to escape some characters Escaping special characters in a SQL LIKE statement using sql parameters.

like image 164
AllenG Avatar answered Oct 20 '22 01:10

AllenG


The %s should be part of the search string, not the query.

string CommandText = "SELECT holiday_name "
                + "FROM holiday "
                + "WHERE holiday_name LIKE @name";
Connection = new SqlConnection(ConnectionString);

try
{
    Connection.Open();
    Command = new SqlCommand(CommandText, Connection);
    string name = "%" + HolidayTextBox.Text + "%";
    Command.Parameters.Add(new SqlParameter("@name", name));
like image 2
Tim S. Avatar answered Oct 20 '22 02:10

Tim S.