How to get exact match in SELECT

Question

I am new to coldfusion and am coming from a VBScript/ASP background using MySQL.

In this project I am doing I need to use MS Access. I have a table with a 50 character text field that contains a randomly generated "code" such as 8LGfkOsLrBKOaKJZo3fxglKSpBGydxKou66x9qF6LIPQIJLxfe.

I need to do an exact match on that code using MS Access and SELECT. I have a function thats purpose to see if the code already exists in the table. The is as follows:

<cffunction name="IsJobKeyUnique" access="public" returntype="boolean" output="false">
<cfargument name="jk" type="string" required="yes">
<cfset ret=True>
<cfquery name="temp" datasource="lrs">
SELECT * FROM jobkeys WHERE jobkey="#ARGUMENTS.jk#"
</cfquery>
<cfdump var="temp">
<cfif temp.RecordCount NEQ 0>
<cfset ret=False>
</cfif>
<cfreturn ret>
</cffunction>

However, the query is not case sensitive. In testing, I changed the last to characters from "fe" to "FE" and it still determines the strings to be equal.

I checked in MS Access to see if I could change the field type of BINARY or soemthing other than simply text but didn't see any options to do so.

Any help would be appreciated.

craigkaminsky · Answer

Would CF's Compare() function work for you? It does a case-sensitive comparison of strings but I can see where it might be an issue since you're looking to do the comparison during the select.

Here's a link to an MS article that might help for doing case-sensitive string comparisons at the DB level:
http://support.microsoft.com/?id=209674

Based on that article, something like this might be worth trying (I don't have Access available to test so this may completely bomb!):
<cfquery name="temp" datasource="lrs">
SELECT * FROM jobkeys WHERE StrComp(jobkey,"#ARGUMENTS.jk#") = 0
</cfquery>

The thing I noted from the MS URL above is that the Asc() function seems pretty useless. It only grabs the ASCII value for the first character in a string -- not too helpful in a lot of cases! StrComp() seems to be what you want but I don't know if it will run inside of CFQUERY or if you have to add a function in Access to accomplish this.

Sign up

To post, reply, or follow discussions, please sign in with your Adobe ID.

Sign in to Adobe Community

To post, reply, or follow discussions, please sign in with your Adobe ID.

Scanning file for viruses.

This file cannot be downloaded