I am back with another technical post! This time, it is with regards with the SQL ‘LIKE’ statement in SQLDatasources which are available in .NET development. Recently, I was having trouble figuring out how you can actually use a SQL statement such as

SELECT * FROM products WHERE productName LIKE ‘%Computers%’

inside a SQLDatasource in an ASP.NET project. Unlike SQL Server, MySQL does not understand the syntax if you use the ‘+’ symbol and put it this manner:

<asp:SqlDataSource ID=”SqlSearchResults” ConnectionString=”<%$ myConnString %>” ProviderName=”<%$ ConnectionStrings:myConnString.ProviderName %>” runat=”server” SelectCommand=”SELECT * FROM product WHERE productName LIKE ‘%’ + @productName + ‘%’)”>
</asp:SqlDataSource>

That is where the use of the CONCAT statement in MySQL comes to play. As the MySQL Database engine does not understand the ‘+’ symbol which we frequently use in SQL Server, CONCAT will help to replace this irregularity in MySQL. So, to get it to work, just modify the data source as follow:

<asp:SqlDataSource ID=”SqlSearchResults” ConnectionString=”<%$ myConnString %>” ProviderName=”<%$ ConnectionStrings:myConnString.ProviderName %>” runat=”server” SelectCommand=”SELECT * FROM product WHERE productName LIKE CONCAT(‘%’, @productName, ‘%’)”>
</asp:SqlDataSource>

With this, your problems with mySQL LIKE will be solved immediately! Hope this helps some people out there… ;)