How do I search for a value in an integer table using LIKE and <cfqueryparam>?

Question

I have a query similar to this:

SELECT itemID FROM itemTable WHERE itemID LIKE '%123%'

itemTable is of type INT. This query works just fine by itself, as it would select '12345' and '0912398' and so on...

The problem occurs when I try to use

Let's say var searchValue = 123

If I try: <cfqueryparam cfsqltype='cf_sql_integer' value="'%#searchValue#%'" >
I get *Invalid data '123' for CFSQLTYPE CF_SQL_INTEGER.*

If I try: <cfqueryparam cfsqltype='cf_sql_varchar' value="'%#searchValue#%'" >
I get java.lang.Integer cannot be cast to java.lang.String

I've tried using CAST in my SQL, and also using toString(searchValue) in various places, but I always end up with one of the error messages above. Is there any way to search and integer table using CFQUERYPARAM?

EDIT: Below is the actual code I am trying to use...

CFSCRIPT Code:

var searchValue=123;
searchFilterQuery(qry=qItemResults, field="itemID", value=searchValue,cfsqltype="cf_sql_varchar");

CFC Function:

<!--- FILTER A QUERY WITH SEARCH TERM --->
<cffunction name="searchFilterQuery" access="public" returntype="query" hint="Filters a query by the given value" output="false">
    <!--- ************************************************************* --->
    <cfargument name="qry"          type="query"    required="true" hint="Query to filter">
    <cfargument name="field"        type="string"   required="true" hint="Field to filter on">
    <cfargument name="value"        type="string"   required="true" hint="Value to filter on">
    <cfargument name="cfsqltype"    type="string"   required="false" default="cf_sql_varchar" hint="The cf sql type of the value.">
    <!--- ************************************************************* --->
    <cfset var qryNew = QueryNew("")>
    <cfquery name="qryNew" dbtype="query">
        SELECT *
            FROM arguments.qry
            WHERE #trim(arguments.field)# LIKE <cfqueryparam cfsqltype="#trim(arguments.cfsqltype)#" value="#trim(arguments.value)#">
    </cfquery>
    <cfreturn qryNew>
</cffunction>

RE: I get *Invalid data '123' for CFSQLTYPE CF_SQL_INTEGER.* .. That is because CF expects your value to be a valid integer like 123 or 212. But what you are actually passing in is the literal string: '%123%'. Obviously not an integer. Hence the error. — Leigh

Adam Cameron Adam Cameron · Accepted Answer · 2011-09-22T16:52:42

You need to do this, I reckon:

remove the quotes as people have said
use a VARCHAR param, not an INTEGER, as the % symbols make the value you're passing not an INTEGER
CAST the integer column as a VARCHAR on the DB side of things. The DB could possibly do this automatically for you, but I reckon it's better to be explicit about these things.

SELECT itemID FROM itemTable WHERE CAST(itemId AS VARCHAR) LIKE <cfqueryparam cfsqltype='cf_sql_varchar' value="%#searchValue#%">

How do I search for a value in an integer table using LIKE and <cfqueryparam>?

2 Answers