syntax for "LIKE" in parameterized query

Question

User-1242214802 posted

Hi All: I have a datatable in a dataset that I want to add a LIKE query to, but I can't seem to get the syntax right.

I'm trying 

SELECT  lastName
FROM   members
WHERE  lastName LIKE  @lname + '%'

but it's not returning any results unless I enter the full name...what am I missing?

I also tried

WHERE  lastName LIKE  (@lname + '%')

and

WHERE  lastName LIKE ('' + @lname + '%')

Answer 1

User-1360095595 posted

That looks right. If you replace @lname by a hardcoded value, does it work?

How are you specifying the @lname parameter? 

Answer 2

User281315223 posted

The query you are essentially using would be considered a "Starts-With" query as you are only using the wildcard character '%' to match contents after your parameter :

SELECT lastName
  FROM members
 WHERE lastName 
  LIKE @lname + '%'

You can see a full example here using the following data :

and executing the following query :

-- Define your parameter (example)
DECLARE @lname AS VARCHAR(64)
SET @lname = 'Mc'

SELECT lastName
  FROM members
 WHERE lastName 
  LIKE @lname + '%'

yields :

This code should be correct.

Could you post an example of the code that you are currently using or how you are adding these parameters?

Answer 3

User1918509225 posted

Hi dotnetnoob,

For your problem,i write  a sample for:

            string connstring = "xx";
            SqlConnection conn = new SqlConnection(connstring);
            conn.Open();
             string name = "ke";
           //if you want to find UserName Column'value contains  "ke", try the below
            string sql = "select * from Employee Where UserName like '%"+name+"%' ";

          //if you want to find UserName Column'value start with"ke", try the below
            string sql = "select * from Employee Where UserName like '"+name+"%' ";

            SqlCommand cmd = new SqlCommand(sql, conn);
             SqlDataAdapter sda = new SqlDataAdapter(cmd);
            DataSet ds = new DataSet();
            sda.Fill(ds, "aa");
            GridView1.DataSource = ds;
            GridView1.DataBind();

Best Regards,
Kevin Shen.

Answer 4

User-1569093213 posted

To Search Lastname Starting with Chracter  @lname
"Select lastName" From members Where lastname LIKE '"+@lname+"%'";

To search Lastname Which Contains the Character @lname
"Select lastname From members where lastname like '%"+@lname+"%'";

To Search lastname Which Ends With Chracter @lname
"Select lastname From members where lastname like '%"+@lname+"%'";

Hope this will help you