How to add char type SqlParameter in SQL LIKE % %?

Question

Can't get this working, how to add a parameter in SQL LIKE % % when its type is char?

SQL.Append("(Code LIKE @Code)");
var sqlParameter = new SqlParameter("@Code",
                      System.Data.SqlDbType.Char, 
                      10,
                      System.Data.ParameterDirection.Input,
                      true, 
                      0, 
                      0, 
                      "Code", 
                      System.Data.DataRowVersion.Current, 
                      "'%300%'");

No error message, the query just doesn't return anything with a parameter. But with dynamic SQL it works (Code LIKE '%300%') — user405723
– user405723, Commented Apr 20, 2015 at 14:46

Community · Accepted Answer · 2017-05-23 11:43:34Z

2

There are two things wrong with your code, the first Soner pointed out, you need to remove the single quotes from your string. The second is the fact that you are using System.Data.SqlDbType.Char as the datatype and passed in a size of 10 to the constructor. This will pad out 10 spaces at the end of your string, effectively turning your string in to "'%300%' "

Use SqlDbType.VarChar instead. Also you don't need all those parameters in the constructor, just fill in the 3 you need then use the .Value property of the parameter you created.

SQL.Append("(Code LIKE @Code)");
var sqlParameter = new SqlParameter("@Code", SqlDbType.VarChar, 10);
sqlParameter.Value = "%300%";

Also, another trick that may be useful to you. If you want to always put the % on both sides but don't want the user to have to provide them you can put them in your query and still use parameters.

string code = "300";

SQL.Append("(Code LIKE '%' + @Code + '%')");
var sqlParameter = new SqlParameter("@Code", SqlDbType.VarChar, 10);
sqlParameter.Value = code;

edited May 23, 2017 at 11:43

CommunityBot

11 silver badge

answered Apr 20, 2015 at 15:09

Scott Chamberlain

128k37 gold badges299 silver badges447 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user405723 Over a year ago

Big thanks! With the SqlDbType.VarChar it worked like a charm.

Soner Gönül · Accepted Answer · 2015-04-20 14:46:20Z

1

Try to use your parameter value without single quotes. I think they make your value part as a string literal instead of parameter.

"%300%"

answered Apr 20, 2015 at 14:46

Soner Gönül

99.1k103 gold badges224 silver badges375 bronze badges

2 Comments

Scott Chamberlain Over a year ago

@Soner I think the problem also comes from the fact that he is using SqlDbType.Char and it is padding his string with spaces which the like is searching for.

Soner Gönül Over a year ago

@ScottChamberlain Exactly, that was my second thought. If you looking for 300 in your column, sounds like you should not choose Char type at all.

Collectives™ on Stack Overflow

How to add char type SqlParameter in SQL LIKE % %?

2 Answers 2

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related