The result of this query is to be able to input and get a query returned (all while ignoring case).
此查询的结果是能够输入并获取返回的查询(所有这些都忽略大小写)。
For example:
If I have these columns in my table-> id, first name, last name
如果我在table-> id,first name,last name中有这些列
For id
I can type in 151, 442, 112 and it should return my results
我可以输入151,442,112,它应该返回我的结果
For first name
为名字
I can type in JiM, jim or JIM and it should return my results
我可以输入JiM,jim或JIM,它应该返回我的结果
For last name
为了姓氏
I can type in Smith, smith, SMITH or SmiTH and it should return my results
我可以输入Smith,smith,SMITH或SmiTH,它应该返回我的结果
The aim is to provide case-insensitivity to both the user (who is inputting the data, and the database - because each record is unique to the database so I shouldn't need to worry if I type in a first/last name combination such as jim smith or JIM SMITH)
目的是为用户(输入数据和数据库)提供不区分大小写的功能 - 因为每条记录对数据库都是唯一的,所以如果输入名字/姓氏组合我就不用担心作为吉姆史密斯或吉姆史密斯)
This is within my WHERE clause
这是在我的WHERE子句中
WHERE #firstname# LIKE <cfqueryparam cfsqltype="cf_sql_varchar" value="%#firstnamevar#%" />
My question is do I need to use lower(), LCASE(), a mixture of these two or something completely different to find a solution to this question?
我的问题是我是否需要使用lower(),LCASE(),这两者的混合或完全不同的东西来找到这个问题的解决方案?
2 个解决方案
#1
2
You need to use UPPER right before the database column and UCase right before the param value. Also, you dont need pound signs around firstname. Like this:
您需要在数据库列之前使用UPPER,并在param值之前使用UCase。此外,你不需要名字周围的英镑符号。喜欢这个:
WHERE UPPER(firstname)
LIKE <cfqueryparam cfsqltype="cf_sql_varchar" value="%#UCase(firstnamevar)#%" />
#2
1
If this is a sql server database you don't have to do anything because it does case insensitive searches on varchar columns.
如果这是一个sql server数据库,则不必执行任何操作,因为它对varchar列执行不区分大小写的搜索。
Otherwise, you apply the upper() function to the sql field and ucase() to the ColdFusion variable. Something like this:
否则,将upper()函数应用于sql字段,将ucase()应用于ColdFusion变量。像这样的东西:
where upper(firstname)
like <cfqueryparam cfsqltype="cf_sql_varchar" value="%#ucase(firstnamevar)#%>
However, this will be slow. It might be worthwhile to store an uppercase version of the field in your db, with an index. Then you can do this:
但是,这将是缓慢的。使用索引在db中存储字段的大写版本可能是值得的。然后你可以这样做:
where upperCaseFirstName
like <cfqueryparam cfsqltype="cf_sql_varchar" value="%#ucase(firstnamevar)#%>
By the way, you posted this,
顺便说一句,你发布了这个,
WHERE #firstname#
LIKE <cfqueryparam cfsqltype="cf_sql_varchar" value="%#firstnamevar#%" >
Unless you have a variable named firstname, the octothorps will cause an error.
除非你有一个名为firstname的变量,否则octothorps会导致错误。
#1
2
You need to use UPPER right before the database column and UCase right before the param value. Also, you dont need pound signs around firstname. Like this:
您需要在数据库列之前使用UPPER,并在param值之前使用UCase。此外,你不需要名字周围的英镑符号。喜欢这个:
WHERE UPPER(firstname)
LIKE <cfqueryparam cfsqltype="cf_sql_varchar" value="%#UCase(firstnamevar)#%" />
#2
1
If this is a sql server database you don't have to do anything because it does case insensitive searches on varchar columns.
如果这是一个sql server数据库,则不必执行任何操作,因为它对varchar列执行不区分大小写的搜索。
Otherwise, you apply the upper() function to the sql field and ucase() to the ColdFusion variable. Something like this:
否则,将upper()函数应用于sql字段,将ucase()应用于ColdFusion变量。像这样的东西:
where upper(firstname)
like <cfqueryparam cfsqltype="cf_sql_varchar" value="%#ucase(firstnamevar)#%>
However, this will be slow. It might be worthwhile to store an uppercase version of the field in your db, with an index. Then you can do this:
但是,这将是缓慢的。使用索引在db中存储字段的大写版本可能是值得的。然后你可以这样做:
where upperCaseFirstName
like <cfqueryparam cfsqltype="cf_sql_varchar" value="%#ucase(firstnamevar)#%>
By the way, you posted this,
顺便说一句,你发布了这个,
WHERE #firstname#
LIKE <cfqueryparam cfsqltype="cf_sql_varchar" value="%#firstnamevar#%" >
Unless you have a variable named firstname, the octothorps will cause an error.
除非你有一个名为firstname的变量,否则octothorps会导致错误。