如何使用LIKE和<cfqueryparam>在整数表中搜索值？

Question

我有一个类似于这样的查询：

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

itemTable 的类型是 INT。这个查询本身可以正常工作，因为它会选择 '12345' 和 '0912398' 等等...

问题出现在我尝试使用：

假设 var searchValue = 123

如果我尝试： 我会得到 *Invalid data '123' for CFSQLTYPE CF_SQL_INTEGER.*的错误。

如果我尝试： 我会得到 java.lang.Integer cannot be cast to java.lang.String 的错误。

我尝试在我的 SQL 中使用 CAST，以及在各个地方使用 toString(searchValue)，但总是会得到上面的错误消息。有没有办法使用 CFQUERYPARAM 来搜索整数表？

编辑：以下是我试图使用的实际代码...

CFSCRIPT 代码：

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

CFC功能：

<!--- 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>

- eterps

---

回复：“我得到了 Invalid data '123' for CFSQLTYPE CF_SQL_INTEGER. 的错误” .. 这是因为 ColdFusion 期望您的 value 是一个有效的 integer，例如 123 或者 212。但事实上，你正在传递的是字面字符串：'%123%'。显然不是整数。因此会出现错误。 - Leigh

Answer 1

我认为你需要这样做：

• 如其他人所说，去掉引号

• 使用VARCHAR参数，而不是整数，因为%符号会使传递的值不是整数

• 在数据库端将整数列强制转换为VARCHAR。数据库可能可以自动完成此操作，但我认为明确说明这些事情更好。

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

- Adam Cameron

---

没错。CAST(#trim(arguments.field)# as varchar) 完美无缺。我以为我之前已经尝试过 CAST 了，但我可能弄错了语法，或者漏掉了引号之类的东西。谢谢亚当和其他所有人！ - eterps

Answer 2

如果您将使用varchar的地方的撇号去掉，那么它就可以工作。

- Kristian82

---

不应在任何一个地方使用单引号。如果需要，DBMS会负责添加引号。当使用cfqueryparam时，您永远不需要为值添加引号。 - Jason Dean

你说得对，我正准备稍微补充一下我的答案。基本上，你现在是将撇号作为值的一部分传递。 - Kristian82

从varchar语句中去掉撇号结果会出现相同的错误： “java.lang.Integer不能转换为java.lang.String”使用cf_sql_integer从语句中删除撇号会得到以下结果： “遇到“itemID LIKE 123”的错误。条件表达式不正确，应为[like|null|between|in|comparison]条件之一。” - eterps

使用 cf_sql_varchar 进行测试，看看会发生什么。 - Jason Dean

@eterps - 我使用cf_sql_varchar（隐式转换）时，它对我很好用。但是看到你的实际sql就能更好地理解为什么它对你不起作用了。似乎有些东西是不同的.. - Leigh