odbc_prepare with parameters

Recently I was trying to use the odbc_prepare with parameters. It worked just fine till I used wildcards in the parameter. Some how it always return "No rows found".

The query goes like this

$pquery=odbc_prepare($conn,"select * from mytable where mytitile like ?");

$result=odbc_execute($pquery,array("title%"));

Interestingly, if the field size is small, let says 4, I could use "xx%%" and the query produce results. If I just use "xx%" it returned "No rows found".

Searching the internet, I could not find informations that pertain to the exact problem. However, while doing a search for "mssql parameter wildcard", I came across a microsoft news group article about wildcards in field type "char" and "varchar".

It looked like mssql responds differently to wildcards in "char" type fields and "varchar" type fields when binding parameters.

"%" will work just like "_" in "char" type fields whereas "varchar" type fields will treat them differently.

The solution to "char" type fields wildcard searching using "%" is simple. Just use "cast(expression as Varchar)". So your prepare statement will look like

$pquery=odbc_prepare($conn,"select * from mytable where cast(mytitile as varchar) like ?");

If you have control of the table, I should suggest you changing the field to varchar instead.