We have a requirement to enable LIKE predicate (wildcard) searches on a field that is based on an INT datatype. All examples in the help are based on STRING data types,which makes sense since that's where its mostly applied. Can you tell me how I can implement a LIKE search against an INT? Its the equivalent of the following sql stmt SELECT * FROM Orders WHERE OrderID Like '11%'

Once you get an answer from support I would still be careful, and making sure performance and results are what you want.

From the SqlServer docs on Like

If any one of the arguments is not of character string data type, the SQL Server Database Engine converts it to character string data type, if it is possible.

To me this sounds like you may end up doing a table scan as the engine will have to convert each rows value to a string before it can evaluate whether it is like x or not.

I didn't know that you can do that with "LIKE". Apparently it's supported out of the box on LLBLGen. See this:

var orders = new EntityCollection<OrderEntity>();

var filter = new RelationPredicateBucket();
filter.PredicateExpression.Add(OrderFields.OrderId %  "1025%");

using (var adapter = new DataAccessAdapter())
{
    adapter.FetchEntityCollection(orders, filter);
}

It actually compiles and generates this SQL:

SELECT [Northwind].[dbo].[Orders].[CustomerID] AS [CustomerId],
       ...
FROM   [Northwind].[dbo].[Orders]
WHERE  ([Northwind].[dbo].[Orders].[OrderID] LIKE @p1) 
@p1 is "1025%"

SQL Profiler reported this executed SQL:

exec sp_executesql N'SELECT [Northwind].[dbo].[Orders].[CustomerID] AS [CustomerId], 
... 
FROM [Northwind].[dbo].[Orders]   
WHERE ( [Northwind].[dbo].[Orders].[OrderID] LIKE @p1)',N'@p1 nvarchar(5)',@p1=N'1025%'

So, just use it like that. BTW, the warning posted by Al is also a good point you should consider.

To speed this up, you could opt for adding a second field, which you fill with the string variant of the int you're searching for, and add an index on that. You then use the predicate on that field. You could use a calculated field for this in the table (using a cast(intfield)). This should use the index.

Thanks everyone for the very useful information ! Appreciate it.